Using A Cell To Reference The Name Of Sheet In A Formula For Google Sheets

Richard Ennis picture Richard Ennis · Oct 11, 2014 · Viewed 60.1k times · Source

is it possible to place the name of the first worksheet in a cell and then use it in a formula to reference information for my second sheet?

I am using Google Spreadsheets, and I have one worksheet that houses scheduling data for employees in columns for each day of the week. The second worksheet builds a payroll report, and directly references columns in the first sheet like so:

='Sales Report - WB 10.06'!B3

My question is, is it possible to place the name of the first worksheet in a cell ('Sales Report - WB 10.06') and then use it in a formula to reference information for my second sheet?

My current issue is that I have to create a new schedule and a new payroll sheet for 10 different regions on a weekly basis, and duplicating and changing the formulas in 70 columns to reference the correct sheet will become very tedious.

It would be much easier to just update a cell in the spreadsheet so that the formulas point to the correct worksheet to reference data from.

Answer

AdamL picture AdamL · Oct 13, 2014

Yes, by using the INDIRECT function:

A1: Sales Report - WB 10.06

Then you can use:

=INDIRECT("'"&A1&"'!B3")