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

59
votes

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.

2
google-sheets

2 Answers

98
votes

Yes, by using the INDIRECT function:

A1: Sales Report - WB 10.06

Then you can use:

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

0
votes

Hi just used this with MATCH "=MATCH($C10,indirect("'"&B$4&"'!$A:$A"),0)

Cell B4 is a date that changes, MATCH checks sheet name from B4 (A Date) column A1 to A returning the position of whatever you have in C10

So useful to have a variable as a Sheet name Many thanks.