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

 

[Paul Ked]

Is the sheet just called 2 or Sheet2? Can't see the sheet tabs.

 

[Guidestone]

Okay, I modified the formula and got a little bit closer, but I'm still not there.

= MAX(INDIRECT("'"&A5&"'!")&INDIRECT(C5:C18))

= MAX(INDIRECT("'" & A5 & "'!" & "C5:C18")) works for me, have you included the quotes around C5:C18?

 

[Guidestone]

it's called 02

Is the sheet just called 2 or Sheet2? Can't see the sheet tabs.

 

[Paul Ked]

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.

 

[Guidestone]

Just did, no change.

https://drive.google.com/file/d/1vv0MupA5X6uDcy_cPTmbN1YY-m7ie4fx/view?usp=sharing

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.

 

[Paul Ked]

Can't see your sheet name in the image.

 

[Guidestone]

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

Can't see your sheet name in the image.

 

[Paul Ked]

Change the formula back to = MAX(INDIRECT("'" & A5 & "'!" & "C5:C18"))

 

[Guidestone]

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

Change the formula back to = MAX(INDIRECT("'" & A5 & "'!" & "C5:C18"))

 

[Paul Ked]

Don't know what else to suggest, it works for me, see https://drive.google.com/open?id=1HIIKWCxOlpGNPfLw4Vxfx3CZAVyQoKEn for proof.