MAX across worksheet, return text value

[andrew]

Hi, need some help.

I have 20 worksheets, with an additional sheet as the 'Summary' page. I have
a formula in cell A2 which returns the MAX value of the same cell in 20
worksheets (sandwiched between 'dummy' worksheets titled 'Start' and 'End').
Formula is =MAX(Start:End!$N$1)

The value returned will always be a numerical. For the cell (B2) in
'Summary', i would like it to display the text of cell AA in the 20
worksheets which matches the MAX value returned. Is this possible?

 

[Joel]

In A2 type something like this

=CONCATENATE("The Toal Value is ",A1)

or simply

="The Toal Value is "&A1

Make sure you have the equal sign.

 

[Gary''s Student]

AA is a column and not a cell.

 

[Gary''s Student]

First enter this UDF:

Public Function whichsheet(v As Variant) As String
'
' first get the sheets to look at
'
istart = 0
iend = 0
For i = 1 To Sheets.Count
If Sheets(i).Name = "Start" Then
istart = i
End If
If Sheets(i).Name = "End" Then
iend = i
End If
Next
'
' now find the sheet with v in N1
'
For i = istart To iend
If Sheets(i).Range("N1").Value = v Then
whichsheet = Sheets(i).Name
Exit Function
End If
Next
End Function

The UDF will displace the sheetname of the sheet containing the required
value in cell N1 of the sheet

In B2 enter:

=INDIRECT(whichsheet(A2) & "!" & "AA1")

This will display the contents of cell AA1 in the proper worksheet.

 

[andrew]

Hi, i tried the UDF but it returned #NAME error. Any possible way without
using macros/UDF?

 

[andrew]

just to add, it managed to retrieve the text, BUT incorrect one (this is
after i saved the macro, and re-opened the file).

In addition to cell A2 in Summary, there are also A3-A10 which calculates
various options (A2 was MAX, A3 looks for MIN, A4 looks for AVERAGE, etc). I
used the =INDIRECT(whichsheet(A2) & "!" & "AA1") and replaced the A2 with
A3-A10 for the corresponding result. Unfortunately all the returned value
were incorrect, some gave #REF error while others retrieved the wrong text
result...

Any ideas?

 

[Gary''s Student]

We need to debug this.

With your MAX, MIN, etc in A2,A3,A4,etc.

In some un-used cells, enter:

=whichsheet(A2)
=whichsheet(A3)
=whichsheet(A4)
=whichsheet(A5) etc.

This will verify the correct sheets are being picked up. You see, I am a
little worried about AVERAGE. If we look across all the sheets looking for
the AVERAGE, we may not find it at all.

 

[andrew]

hi gary, i tested with the verification formulas provided. Unfortunately it
didn't work for most of them (i've only used MIN and MAX cells to test).

they either returned the wrong cell/worksheet, #NAME or in 1 particular
case, #REF. Any ideas?

 

[andrew]

can anyone assist please?

hi gary, i tested with the verification formulas provided. Unfortunately it
didn't work for most of them (i've only used MIN and MAX cells to test).

they either returned the wrong cell/worksheet, #NAME or in 1 particular
case, #REF. Any ideas?