Jan 18 2021 06:03 PM
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.
Jan 18 2021 06:31 PM
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
Jan 19 2021 09:54 AM
Jan 19 2021 01:00 PM
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.
Jan 19 2021 02:55 PM
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.
Jan 19 2021 03:32 PM
The data are in different tabs (sheets) in my Excel workbook and sometimes they are at different cell addresses.
I'm not familiar with Excel Tables. Can you explain a bit further?
Jan 19 2021 04:14 PM
That same educational resource I sent you to earlier for info on INDIRECT can help you with questions like this on the nature of Excel Tables. https://exceljet.net/excel-tables
Spend some time there. Another good resource for getting more acquainted withe Excel is YouTube.