formula for referencing Excell sheets from a Newb!

[Dmachine]

Greetings,

I have a Summary sheet with a formula that gives me the largest value between 3 other sheets in cell c4 for each sheet. I would like the summary sheet to give the name of the the sheet with the largest value in C4 not the value in cell c4.

The current formula I have is

=max(MacBook:DellXPS!C4)

Thanks.

 

[circledchicken]

Hi,

Here's one way to do it for 3 sheets (although not the most elegant):
You should change the bold elements to the name of your middle sheet.

=CHOOSE(MATCH(1,FREQUENCY(MAX(MacBook:DellXPS!C4),MacBook:DellXPS!C4),0),
MID(CELL("filename",MacBook!C4),FIND("]",CELL("filename",MacBook!C4))+1,256),
MID(CELL("filename",Sheet2!C4),FIND("]",CELL("filename",Sheet2!C4))+1,256),
MID(CELL("filename",DellXPS!C4),FIND("]",CELL("filename",DellXPS!C4))+1,256))

The sheet names can change but the sheets would have to stay in the same relative positions i.e. "MacBook" first, "Sheet2" (or what you've named it) second, and "DellXPS last.

 

[T. Valko]

Greetings,

I have a Summary sheet with a formula that gives me the largest value between 3 other sheets in cell c4 for each sheet. I would like the summary sheet to give the name of the the sheet with the largest value in C4 not the value in cell c4.

The current formula I have is

=max(MacBook:DellXPS!C4)

Thanks.

One way...

List the sheet names in a range of cells:

• A3 = MacBook
• A4 = Compaq
• A5 = DellXPS

Then, this array formula**:

=INDEX(A3:A5,MATCH(MAX(N(INDIRECT("'"&A3:A5&"'!A1"))),N(INDIRECT("'"&A3:A5&"'!C4")),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.