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
