Google Sheets – Use Cell Value to Define Sheet Reference

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.

You should mark column AG in some way so it can be identified as the column that indirect needs. For example, by placing "Data" in the first row of that column.

Then you can use match to find the number of that column, which becomes a part of cell reference in R1C1 notation.

=indirect($A$1 & " Data!R3C" & match("Data", 1:1, 0), false)

This formula refers the cell in 3rd row of the column with header "Data", on the sheet specified in cell A1.