MAX across worksheet, return text value
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?
"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200800
"andrew" wrote:
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?
"andrew" wrote:
Hi, i tried the UDF but it returned #NAME error. Any possible way without
using macros/UDF?
"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200799
"andrew" wrote:
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?
|