Ask Your Question

How can I use a sheet name in formulas? SHEET doesn't work!

asked 2016-09-20 19:30:39 +0200

Jimtech gravatar image

updated 2017-12-10 17:06:03 +0200

erAck gravatar image

Hi, I am dumping data into sheets and naming them with dates, I want to have a master sheet that pulls info from lots of various sheets but I only want to type the sheet name once, not 25 times inserting it into formulas, by typing the sheet name into one cell and a formula referencing it, but it doesn't seem to work. here is an example.

this works


but I want to use some thing like


where A32 is a sheet name?

Any thoughts on why this won't work?

Many thanks


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-09-20 20:13:21 +0200

erAck gravatar image

updated 2016-09-20 20:15:27 +0200

It does not work because A32 is (of course) expected to be a sheet name in this case. You can use the INDIRECT function with a string concatenated of A32&".C1383:C1442" but that 1. will make things slower 2. will force you to type even more 3. does not adapt references when inserting/deleting columns or rows

edit flag offensive delete link more


does that mean i would have to put something like this ? =AVERAGE(INDIRECT("'"&A56&"'").C1383:C1442) and put the sheet name in cell A56? Many Thanks Jim

Jimtech gravatar imageJimtech ( 2016-09-20 21:34:21 +0200 )edit

No, =AVERAGE(INDIRECT(A56&".C1383:C1442"))

erAck gravatar imageerAck ( 2016-09-21 13:22:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-20 19:30:39 +0200

Seen: 207 times

Last updated: Sep 20 '16