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

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

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

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

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