93

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.

Jonathan
  • 1,542
  • 3
  • 16
  • 24
Richard Ennis
  • 939
  • 1
  • 6
  • 3

2 Answers2

149

Yes, by using the INDIRECT function:

A1: Sales Report - WB 10.06

Then you can use:

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

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • 14
    Here's a refinement: if you want the B3 to change as you fill down or fill right, use something like =INDIRECT(ADDRESS(ROW(),COLUMN(),4,TRUE,$A$1)). Also, you can add to or subtract from ROW() and COLUMN() to refer to cells in the other sheet that are offset from the current cell's address by fixed amounts. – circlepi314 Feb 07 '19 at 21:02
  • What if you want the A1 to not change? When I tried this it worked well, but as I dragged the formula across the columns, the A1 changed to B1, etc. A1 is the only place with the sheet name. Doing !A1 did not work. – Liam Nov 18 '19 at 02:32
  • 1
    @Liam Put a dollar sign before any column or row that shouldn't change. – A_P Mar 09 '20 at 01:15
  • just for clarity in the script above `"'"` is adding a single quote then `&A1&` is concatenating the cell name. I found it clearer to write it: `=sum(INDIRECT("'" &A2& "'!f2:f2000"))` – dcsan Mar 10 '21 at 17:27
  • Trying to get this to work with vlookup but isn't working. Not compatible? – motorbaby Mar 29 '21 at 00:48
0

You can use 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.

ahinkle
  • 2,117
  • 3
  • 29
  • 58
Mark Hull
  • 1
  • HI @MarkHull, "this" is not a helpful reference. Please clarify what exactly you are referring to. Also have a look at https://stackoverflow.com/editing-help on how to format your post more helpfully. Also, I think you are actually only posting this to say "Thanks", instead of answering the question at the top of this page. If you agree please delete the post. If you think this IS an answer to the question please [edit] it to make that more obvious and maybe more according to [answer] and [tour], both links are very recommended reading. Have fun and welcome to StackOverflow. – Yunnosch Apr 06 '21 at 05:50