MAX across worksheet, return text value
 

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?

MAX across worksheet, return text value
 

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.

"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?

MAX across worksheet, return text value
 

AA is a column and not a cell.
--
Gary''s Student - gsnu200799

MAX across worksheet, return text value
 

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?

MAX across worksheet, return text value
 

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?

MAX across worksheet, return text value
 

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?

MAX across worksheet, return text value
 

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?

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?

MAX across worksheet, return text value
 

can anyone assist please?


"andrew" wrote:

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?