Forum Discussion
Summarizing Data across Multiple Workbooks
If I understand correctly the situation (it would help if you could upload a sample of, say, two or three project sheets plus a rough idea of what you want your summary to look like)...if I understand, though, in absence of that, it reminds me of an investment tracking workbook I have, wherein I summarize on one page some of the key data from each individual page.
I make use of the INDIRECT function, to point to each individual sheet. In my case, each sheet is named according to the symbol of the stock (AAPL, MSFT, etc) and that symbol also appears as the reference point for the INDIRECT function. And then there are cell references within each sheet (consistent layout, as you apparently have), so I can have a formula that looks like this =INDIRECT($D6&"!"&E$1) with the first ($D6) pulling in the stock symbol, which is also the name of the sheet, the second (E$1) a cell in the summary sheet that points to a cell in each of the detailed sheets.
There no doubt are other ways to accomplish this, but that's one. If you need further help with this, please upload an anonymous example of what you're working with.
Thanks for your reply. I am not familiar with the INDIRECT Function but will look at it, though I am not sure how I can cycle through and access all my external project workbooks. To help clarify my request, see the image below.
Regards
You might find this reference useful for three-dimensional summing, which is now what it sounds like you want to do. https://bettersolutions.com/excel/formulas/three-dimensional.htm
(FWIW, I read your initial description of a desire to produce a summary quite differently. To me a "summary" of many sheets picks off the salient or concluding or final figures of each sheet and reports them. It might also include a summary sum or two, but that would not be the essence of it.)
