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

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


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 ?
and put the sheet name in cell A56?
Many Thanks

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