Reference a Cell as the Name of a Sheet in Another Workbook

Question

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.

Answer 1

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

• Using IMPORTRANGE, INDIRECT, and CONCATENATE together
• Using IMPORTRANGE as an array formula
• ArrayFormula + multiple IMPORTRANGE + QUERY