I have a Google Apps spreadsheet with three sheets, Y2013, Y2014, and table. On the table sheet I want to place the contents of cell B2 from either Y2013 or Y2014 depending upon which is in the contents of cell A1 on table i.e., A1 is either Y2013 or Y2014. I have tried: =A1!B2 ='&A1&'!B2. What else might I try?

You can use <code>INDIRECT</code> to dynamically refer to sheetnames like this in your example: <pre class="prettyprint"><code>=INDIRECT(CONCATENATE(A1;"!B2")) </code></pre> Call A1 should contain your year number (Y2013 or Y2014). If you want to learn more on how to use indirect you can check this: http://spreadsheetpro.net/how-to-make-a-dynamic-reference-to-a-worksheet-in-excel-and-google-spreadsheets/

Google Apps spreadsheet indirect sheet name?

1 Answers

You can use INDIRECT to dynamically refer to sheetnames like this in your example:

=INDIRECT(CONCATENATE(A1;"!B2"))

Call A1 should contain your year number (Y2013 or Y2014).

If you want to learn more on how to use indirect you can check this: http://spreadsheetpro.net/how-to-make-a-dynamic-reference-to-a-worksheet-in-excel-and-google-spreadsheets/