| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX across worksheet, return text value
AA is a column and not a cell.
-- Gary''s Student - gsnu200799 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Link to text and return text into a formula? | Excel Worksheet Functions | |||
| LOOKUP text return text | Excel Worksheet Functions | |||
| lookup a text cell and return text | Excel Discussion (Misc queries) | |||
| I need to return the name of a worksheet in another worksheet cell? | Excel Worksheet Functions | |||
| Match text to another worksheet and return a certain value | Excel Worksheet Functions | |||
Linear Mode
