Referncing a cell, based on a cell's value in Spreadsheet

Question

I understand how referencing a cell works, example being

=Sheet1.A1

However, if I'm in Sheet2 and cell B1 contains Sheet1, and cell C1 contains A1.

Is there any way to write the reference such that it uses the values from B1 and C1 to determine where the reference should point to?

Answer 1

You need to use ! after the sheet name for the basic formula, like this

=Sheet1!A1

You can use INDIRECT to convert a text reference to an actual one, so for your example try

=INDIRECT(B1&"!"&C1)

Sheet names with spaces (and some other characteristics) also need single quotes around them e.g.'Sheet one'!A1 so to cover all possible sheet names you can change to this version

=INDIRECT("'"&B1&"'!"&C1)