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

 

[Scott Huish]

If you want to reference the current sheet, why not just drop the sheet name?

=MAX(C5:C18)

 

[Richard U]

ASSUMING YOU HAVE THE SHEET NAME STORED IN CELL "A5"

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


Cell A5 will contain the sheet name, the tic in quotes to the left, and the second tic with the exclamation point on the right will complete the sheet reference.

 

[Guidestone]

Because the format will need to be modified by someone else to reference another sheet, and I want the format to be the same for both.

 

[Guidestone]

This doesn't seem to be working because I'm getting a #REF ! for that cell. Its only recognizing the A5 and not actually taking the max from C5:C18. I do believe this is the direction I want to go though.

ASSUMING YOU HAVE THE SHEET NAME STORED IN CELL "A5"

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


Cell A5 will contain the sheet name, the tic in quotes to the left, and the second tic with the exclamation point on the right will complete the sheet reference.

 

[Paul Ked]

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

 

[Guidestone]

Here is what I'm seeing on my end:

[FONT=&quot]https://imgur.com/3HNVWIf

[/FONT]

 

[Guidestone]

Well, apparently I can't upload an image, nor can I provide a link, nor can I share an image, so I'm not quite sure how to show you what's happening on my screen.

 

[Paul Ked]

Upload to Dropbox, Google Drive or similar and post the link

 

[Guidestone]

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