Reference the sheet name in formula as a number

Guidestone

New Member
Joined
Jul 5, 2018
Messages
14
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
If you want to reference the current sheet, why not just drop the sheet name?

=MAX(C5:C18)
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
377
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

New Member
Joined
Jul 5, 2018
Messages
14
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

New Member
Joined
Jul 5, 2018
Messages
14
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

Active Member
Joined
Jun 4, 2015
Messages
434
= MAX(INDIRECT("'" & A5 & "'!" & "C5:C18")) works for me, have you included the quotes around C5:C18?
 

Guidestone

New Member
Joined
Jul 5, 2018
Messages
14
Here is what I'm seeing on my end:

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

[/FONT]
 

Guidestone

New Member
Joined
Jul 5, 2018
Messages
14
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

Active Member
Joined
Jun 4, 2015
Messages
434
Upload to Dropbox, Google Drive or similar and post the link
 

Forum statistics

Threads
1,077,686
Messages
5,335,656
Members
399,032
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top