I have ~400 sheets spread through multiple workbooks (on purpose). Each sheet is the scoring for an entry in an award competition.
I have a master scorecard sheet that grabs the score from each sheet, regardless of what workbook it is in. I have been able to set up the formula so, if I hardcode the name of the sheet in some other workbook, it correctly grabs the data:
=(IMPORTRANGE("LINKtoWORKBOOK","SHEETNAME!CELL"))
But this requires identifying SHEETNAME in every instance of this formula. I would rather create a single column that lists the names of each sheet, and then for a given row, have that row's formulas all look to the cell with the sheet name.
I've tried using INDIRECT but had no luck. I think I am close:
=(IMPORTRANGE("LINKtoWORKBOOK",(INDIRECT(CELLwithSHEETname&"!CELLonTHATsheet"))))
I've tried variations of quotes and moving parenthesis with no luck.
The second argument of IMPORTRANGE should be text value (string) so you can concatenate the sheet name to the cell / range reference. I.E. if A1 has the sheet name use
=IMPORTRANGE("spreadsheet_key",A1&"!A1:Z")
NOTES:
INDIRECT function can't be used because it returns a reference as if it is directly written in the formula, i.e.
=A1
is the same as
=INDIRECT("A1")
but IMPORTRANGE requires that the second argument be a text value, so the above will only work when A1 has something like A1:Z or Sheet1!A1:Z (without the equal or plus sign)
Arrays can't be used as arguments of IMPORTRANGE. In other words, something like
=ARRAYFORMULA(IMPORTRANGE("spreadsheet_key",A1:A10))
doesn't work. In this case the alternative is to use multiple IMPORTRANGE, one for each reference.
Related