Reference the sheet name in formula as a number

[Guidestone]

I'm trying to write the following formula:

=MAX('02'!C5:C18)

But instead of referencing the sheet named "02" I either want to reference a cell that outputs the name or number of the sheet, or have it reference the current sheet (because there are 200 sheets just like this one and they are all the same format, but contain different data). Any idea how I can achieve this?

Thanks,
Chris

 

[Richard U]

https://drive.google.com/file/d/10rwr7xykbCcOSsRMt6X8tEVAE1b998qb/view?usp=sharing

Put the name of your sheet in A5 instead of the sheet number

 

[Paul Ked]

Put the name of your sheet in A5 instead of the sheet number

02 is the name of the sheet!

 

[Richard U]

It doesn't seem to recognize the data in the C column because my red reference box is gone, so I'm not sure where it's pulling the data from.

https://drive.google.com/file/d/1YTXsTM_cPtLkuA07ZNhq_Q14N00dlU5H/view?usp=sharing

Here's a "Hail Mary" move.

The only thing left could be a corrupted worksheet or workbook.

If you copy the data with a copy ==> Pastespecial ==> values into another worksheet, and point the formula to that one, it might work.

If it does, you know you have a corrupted worksheet

 

[Richard U]

02 is the name of the sheet!

Yes "02" is the name of the sheet, but I see he has it as "2". That might make the difference.

If the cell isn't formatted as text, and the VALUE is "2" and not "02" then it might not work

 

[Paul Ked]

See my post #14

Format A5 as TEXT and make sure it has 02 in it, in your image it is shown as 2 so the formula doesn't recognise it as the sheet name.