Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference funct
I'm trying to write a function that returns the worksheet name of a max value
in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference funct
If cell C1 in each sheet contains a numerical value, the formula
=MATCH(2,1/FREQUENCY(MAX(Sheet1:Sheet8!C1),Sheet1:Sheet8!C1)) returns the number of the first sheet containing the maximum value. "PKK" wrote: I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference funct
If you can use sheet names like in your example with the same text string
then just different index number it isn't that hard ="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("' Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0) entered with ctrl + shift & enter will return the sheet name that holds the MAX value, note that if there are multiple max values the first occurrence will be returned. That should answer your second question -- Regards, Peo Sjoblom "PKK" wrote in message ... I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference f
Thanks Lori. That works perfectly for Sheet number. However, let me put in
a new qualifier. What if the sheets are Named and not Sheet1, 2, 3, etc. Do you know a way to return that name? Perhaps use of the Indirect function? "Lori" wrote: If cell C1 in each sheet contains a numerical value, the formula =MATCH(2,1/FREQUENCY(MAX(Sheet1:Sheet8!C1),Sheet1:Sheet8!C1)) returns the number of the first sheet containing the maximum value. "PKK" wrote: I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference funct
A UDF solution that is not as elegant as PKK's formula
Function WheresMax() mymax = 0 For j = 1 To 8 myvalue = Worksheets(j).Range("C1").Value If myvalue mymax Then mymax = myvalue mysheet = Worksheets(j).Name End If Next j WheresMax = mysheet End Function best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "PKK" wrote in message ... I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference f
Thank you Peo. This works perfectly as well. Now I just need to get it
working where the sheets are now named instead of the default Sheet1, 2, 3 etc. "Peo Sjoblom" wrote: If you can use sheet names like in your example with the same text string then just different index number it isn't that hard ="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("' Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0) entered with ctrl + shift & enter will return the sheet name that holds the MAX value, note that if there are multiple max values the first occurrence will be returned. That should answer your second question -- Regards, Peo Sjoblom "PKK" wrote in message ... I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference f
Maybe create a list of sheet names on a sheet, then use
=INDEX(SheetNames,SheetNumber) or define "SheetNames" to refer to "=get.workbook(1)" for a list of all names in the workbook. "PKK" wrote: Thanks Lori. That works perfectly for Sheet number. However, let me put in a new qualifier. What if the sheets are Named and not Sheet1, 2, 3, etc. Do you know a way to return that name? Perhaps use of the Indirect function? "Lori" wrote: If cell C1 in each sheet contains a numerical value, the formula =MATCH(2,1/FREQUENCY(MAX(Sheet1:Sheet8!C1),Sheet1:Sheet8!C1)) returns the number of the first sheet containing the maximum value. "PKK" wrote: I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference f
If you have totally unrelated sheet names of the sheets or rather no number
index with the same string you could put all the sheet names (ALL sheet names, not just first and last) in a range, assume we call that range MySheets =INDEX(MySheets,MATCH(MAX(N(INDIRECT("'"&MySheets& "'!C1"))),N(INDIRECT("'"&MySheets&"'!C1")),0)) entered with ctrl + shift & enter However Lori's solution is better and would work with even less tweaking, assume as above the list with all sheet names is called MySheets =INDEX(MySheets,MATCH(2,1/FREQUENCY(MAX(abc:vbnmh!C1),abc:vbnmh!C1))) will still work plus it is non volatile as opposed to my formula with INDIRECT -- Regards, Peo Sjoblom "PKK" wrote in message ... Thank you Peo. This works perfectly as well. Now I just need to get it working where the sheets are now named instead of the default Sheet1, 2, 3 etc. "Peo Sjoblom" wrote: If you can use sheet names like in your example with the same text string then just different index number it isn't that hard ="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("' Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0) entered with ctrl + shift & enter will return the sheet name that holds the MAX value, note that if there are multiple max values the first occurrence will be returned. That should answer your second question -- Regards, Peo Sjoblom "PKK" wrote in message ... I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return worksheet name containing value found in 3D reference f
Exactly, that's what I ended up doing. Thanks both Lori and Peo!
My only challenge for the future though, is being able to do this with named sheets and no indexing. Since indexing simply returns a text string as typed, it isn't really a reference to the tab in question. As such, it limits options to go and use that return in other functions. However, for now it really helps a lot. Thanks again! "Peo Sjoblom" wrote: If you have totally unrelated sheet names of the sheets or rather no number index with the same string you could put all the sheet names (ALL sheet names, not just first and last) in a range, assume we call that range MySheets =INDEX(MySheets,MATCH(MAX(N(INDIRECT("'"&MySheets& "'!C1"))),N(INDIRECT("'"&MySheets&"'!C1")),0)) entered with ctrl + shift & enter However Lori's solution is better and would work with even less tweaking, assume as above the list with all sheet names is called MySheets =INDEX(MySheets,MATCH(2,1/FREQUENCY(MAX(abc:vbnmh!C1),abc:vbnmh!C1))) will still work plus it is non volatile as opposed to my formula with INDIRECT -- Regards, Peo Sjoblom "PKK" wrote in message ... Thank you Peo. This works perfectly as well. Now I just need to get it working where the sheets are now named instead of the default Sheet1, 2, 3 etc. "Peo Sjoblom" wrote: If you can use sheet names like in your example with the same text string then just different index number it isn't that hard ="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("' Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0) entered with ctrl + shift & enter will return the sheet name that holds the MAX value, note that if there are multiple max values the first occurrence will be returned. That should answer your second question -- Regards, Peo Sjoblom "PKK" wrote in message ... I'm trying to write a function that returns the worksheet name of a max value in a simple 3d reference. Sheets 1 through 8 hold values in cell C1. In a summary page =MAX(Sheet1:Sheet8!C1) returns the max value found in any of those cells. Can a function return the sheet name of the sheet on which that max value was found? Additionally, what errors might be generated if the Max value is on more than one sheet? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, return zero if not found | Excel Worksheet Functions | |||
can lookup return err if no match found | Excel Worksheet Functions | |||
Linear interpolation between two points found using a lookup funct | Excel Worksheet Functions | |||
How do you reference another cell in the criteria of a SUMIF funct | Excel Worksheet Functions | |||
A function to get a variable row reference for range in XNPV funct | Excel Worksheet Functions |