New Contributor

I have a spreadsheet with 25 tabs containing structured data (text and numbers) for 25 separate entities.  I want to create a summary sheet for all 25 entities that references certain cells in each tab and reports the contents or value of that cell in separate columns.  I'd appreciate some help or pointers on how to automate this process, so I don't have to manually go look up each tab.

6 Replies

This is a job for INDIRECT

I have a workbook where I do what you're seeking to do. Here's an example of the formula:

=INDIRECT(\$D4&"!"&C\$1)

essentially this formula, on the summary sheet, builds a cell reference comprising the name of the sheet [found in cell D4 of the summary sheet] and the cell on that sheet [whatever cell reference is found in cell C1 of the summary sheet], and brings the value from there into the summary sheet.

Said another way, INDIRECT causes Excel to read that as =SheetName!B1 or =SheetName!C5

I can copy the formula down to lower rows and across to other columns; it continues to refer to Column D but with different rows containing different sheet names; it continues to refer to Row 1, but Columns C through M [or whatever], each containing different cell references

to help de-code this, let me give you a link to an instructional website: https://exceljet.net/excel-functions/excel-indirect-function

Thank you, man!

I tried it and it works great for my purpose.

The help is much appreciated.

Hey!

So I thought, the data values I was trying to gather from each tab (each sheet) are not always at the exact same cell address.  Some values are (and the INDIRECT function worked well then) but some are not.  I can't do a Lookup because the criterion column is not sorted in any order.

How do I find these specific numbers?

What I mean is,   I'm trying to find the value in the B column when "Total Funding" or "Total Expenses" (or any other text) is in the A column.  These specific text criteria are always in the A column, but they can be in different rows depending on the organization (separate sheet).

Thanks kindly for the help.

What do you mean by 'structured data'.  Are they Excel Tables?

If so you can use INDIRECT to return a column of the table based upon the name of the table concatenated with the column reference.  That way if a table appears in a different place, is a different size or the columns are in a different order, it makes no difference.  An example might be

= INDIRECT(@Tables&"[Date]")

where @Tables is a relative reference to a particular member of a list of Tables and [Date] is a field found within the table.