Ask Your Question
0

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

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

Jimtech gravatar image

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

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

=AVERAGE('20-07-16'.C1383:C1442)

but I want to use some thing like

=AVERAGE(A32.C1383:C1442)

where A32 is a sheet name?

Any thoughts on why this won't work?

Many thanks

Jim

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

erAck gravatar image

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

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

Comments

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 +0100 )edit

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

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

Question Tools

1 follower

Stats

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

Seen: 104 times

Last updated: Sep 20 '16