Forum Discussion
Getting values from the same cell address in different spreadsheets
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
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.