This would be similar to creating a summary worksheet of totals with the various lookup options in Excel. Example: credit card workbook with itemized categories combined with checking account workbook to create a balance sheet, income statement, etc.
As of today it is NOT possible to write formulas with cross-workbook references.
However, given your requirement will request you to explore Relationship fields & Lookup fields in SSDC.
With Relationship field one can relate rows of different sheets even from different workbooks.
E.g. Purchase-Order row related to Order-Item row
Over this relationship we can then define Lookup fields to pull data into a sheet from its related sheet. E.g. Pull all related Department details into Employee sheet against each Employee row.
Once available in the Employee sheet, we can write formulas using both Employee & its Department data.
We can also define a Lookup field over a relationship to perform rollup/aggregate calculations. That is, define a Lookup field in Purchase-Order that computes the Total_Amount for each Purchase-Order row, by summing all linked Order-Item unit prices.
You will find working with Relationships in SSDC more intuitive than using lookup formula-functions & also achieve cross-workbook functionality.
For more details, please refer:
Thanks for reminding me about the embedded row-based database lookup. Makes sense to aggregate lookups like a query. Problem solved. Thanks again.