Ask Your Question
0

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

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

Jimtech gravatar image

updated 2020-08-02 13:08:02 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-02 13:09:02.378438

1 Answer

Sort by » oldest newest most voted
1

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

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

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

erAck gravatar imageerAck ( 2016-09-21 13:22:17 +0200 )edit

Question Tools

1 follower

Stats

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

Seen: 622 times

Last updated: Sep 20 '16