Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MIN/MAX functions and reporting from a different column
Hi all,
I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#2
|
|||
|
|||
spodosaurus wrote:
Hi all, I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am, I'll continue in the morning), but would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Now, If I wanted to do the same thing for a MAX in Column C I'd just change the functions B's to C's...right? okay...must sleep now... Cheers, Ari -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#3
|
|||
|
|||
Yes, that formula would be appropriate and converted as stated.
-- Regards, Tom Ogilvy "spodosaurus" wrote in message ... spodosaurus wrote: Hi all, I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am, I'll continue in the morning), but would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Now, If I wanted to do the same thing for a MAX in Column C I'd just change the functions B's to C's...right? okay...must sleep now... Cheers, Ari -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#4
|
|||
|
|||
Hi!
would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B $100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Correct. Since you are indexing a single column range you can omit the column_number argument. It defaults to 1 when not included. Also, the match_type argument can be one of three choices: -1, 0, 1. Since you're using FALSE this will evaluate to 0 for an exact match and will still work properly. So: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0)) And for the MAX based on column C: =INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0)) Note that if there are duplicate entries for either max or min the above formulas will only return the corresponding value for the first instance. Biff "spodosaurus" wrote in message ... spodosaurus wrote: Hi all, I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am, I'll continue in the morning), but would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Now, If I wanted to do the same thing for a MAX in Column C I'd just change the functions B's to C's...right? okay...must sleep now... Cheers, Ari -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#5
|
|||
|
|||
Biff wrote:
Hi! would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$ B$100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Correct. Since you are indexing a single column range you can omit the column_number argument. It defaults to 1 when not included. Also, the match_type argument can be one of three choices: -1, 0, 1. Since you're using FALSE this will evaluate to 0 for an exact match and will still work properly. So: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0)) And for the MAX based on column C: =INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0)) Note that if there are duplicate entries for either max or min the above formulas will only return the corresponding value for the first instance. Biff Now, if I wanted the values returned by these equations to appear in a separate workbook (I have 18 separate workbooks that I want to take values from and put into one workbook) would I have to add a 'workbook1.xls'! to the beginning of each of the three functions in the equations? Such as: =INDEX('workbook1.xls'!$A$2:$A$100,MATCH('workbook 1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$10 0,0)) "spodosaurus" wrote in message ... spodosaurus wrote: Hi all, I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am, I'll continue in the morning), but would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$ B$100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Now, If I wanted to do the same thing for a MAX in Column C I'd just change the functions B's to C's...right? okay...must sleep now... Cheers, Ari -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#6
|
|||
|
|||
Hi
With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier spodosaurus wrote: Biff wrote: Hi! would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Correct. Since you are indexing a single column range you can omit the column_number argument. It defaults to 1 when not included. Also, the match_type argument can be one of three choices: -1, 0, 1. Since you're using FALSE this will evaluate to 0 for an exact match and will still work properly. So: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0)) And for the MAX based on column C: =INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0)) Note that if there are duplicate entries for either max or min the above formulas will only return the corresponding value for the first instance. Biff Now, if I wanted the values returned by these equations to appear in a separate workbook (I have 18 separate workbooks that I want to take values from and put into one workbook) would I have to add a 'workbook1.xls'! to the beginning of each of the three functions in the equations? Such as: =INDEX('workbook1.xls'!$A$2:$A$100,MATCH('workbook 1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$10 0,0)) "spodosaurus" wrote in message ... spodosaurus wrote: Hi all, I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am, I'll continue in the morning), but would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Now, If I wanted to do the same thing for a MAX in Column C I'd just change the functions B's to C's...right? okay...must sleep now... Cheers, Ari |
#7
|
|||
|
|||
Roger Govier wrote:
Hi With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier It almost works! Excel gives me an error with the second workbook reference in this formula: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) The first and thrid references to Workbook1.xls seem to be okay. I've even tried adding extra parentheses around the second reference, like this: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0)) again, without success :-( spodosaurus wrote: Biff wrote: Hi! would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Correct. Since you are indexing a single column range you can omit the column_number argument. It defaults to 1 when not included. Also, the match_type argument can be one of three choices: -1, 0, 1. Since you're using FALSE this will evaluate to 0 for an exact match and will still work properly. So: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0)) And for the MAX based on column C: =INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0)) Note that if there are duplicate entries for either max or min the above formulas will only return the corresponding value for the first instance. Biff Now, if I wanted the values returned by these equations to appear in a separate workbook (I have 18 separate workbooks that I want to take values from and put into one workbook) would I have to add a 'workbook1.xls'! to the beginning of each of the three functions in the equations? Such as: =INDEX('workbook1.xls'!$A$2:$A$100,MATCH('workbook 1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$10 0,0)) "spodosaurus" wrote in message ... spodosaurus wrote: Hi all, I'm trying to figure out how to get Excel 2003 to report a value from a separate column where the value reported by a MIN or MAX function occurs. For example, say this is my setup: Column A: time Column B: value 1 Column C: value 2 I want to create a function using the MIN and MAX functions for cell ranges in either column B or column C plus I want the value that corresponds to the MIN or MAX value from column A. Basically, I have data in columns B and C that occurs at times listed in column A. I not only need to know the MIN and MAX values for certain ranges in columns B and C but also the times at which these values occur (across hundreds and hundreds of values with multiple MINs and MAXs, so this is not something I want to do manually across multiple workbooks). ummmm Help! TIA, Ari Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am, I'll continue in the morning), but would this be the sort of thing that might possibly work: =INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1) This would find the value in Column A that corresponds to the MIN value for the range in Column B...right? Now, If I wanted to do the same thing for a MAX in Column C I'd just change the functions B's to C's...right? okay...must sleep now... Cheers, Ari -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#8
|
|||
|
|||
Hi
I think your second Workbook reference is superfluous. Try =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) Regards Roger Govier spodosaurus wrote: Roger Govier wrote: Hi With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier It almost works! Excel gives me an error with the second workbook reference in this formula: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) The first and thrid references to Workbook1.xls seem to be okay. I've even tried adding extra parentheses around the second reference, like this: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0)) again, without success :-( |
#9
|
|||
|
|||
Roger Govier wrote:
Hi I think your second Workbook reference is superfluous. Try =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) Gives me a circular error, so it appears I might need it :-/ I'm wondering if I have the workbook references on the correct sides of all the parentheses...? Regards Roger Govier spodosaurus wrote: Roger Govier wrote: Hi With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier It almost works! Excel gives me an error with the second workbook reference in this formula: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) The first and thrid references to Workbook1.xls seem to be okay. I've even tried adding extra parentheses around the second reference, like this: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0)) again, without success :-( -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#10
|
|||
|
|||
spodosaurus wrote:
Roger Govier wrote: Hi I think your second Workbook reference is superfluous. Try =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) Gives me a circular error, so it appears I might need it :-/ I'm wondering if I have the workbook references on the correct sides of all the parentheses...? Okay, starting from he =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) I think the MATCH afunction might actually need extra references to the workbook for its second argument: MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0) This tells it to look in Workbook1.xls for the first argument, but then perhaps it's looking to the workbook that it's in (Workbook19.xls) for the $C$2:$C$100 value? I'm posting this from a separate computer because the one that I'm working on is not networked at present, so bare with me while I speculate then travel back and forth to test things. Regards Roger Govier spodosaurus wrote: Roger Govier wrote: Hi With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier It almost works! Excel gives me an error with the second workbook reference in this formula: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) The first and thrid references to Workbook1.xls seem to be okay. I've even tried adding extra parentheses around the second reference, like this: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0)) again, without success :-( -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
#11
|
|||
|
|||
Hi
Sorry, I think its me being rather sleepy on a Sunday afternoon!! You are right, you do need the second Workbook reference, PLUS a fourth one before the final cell range, otherwise it will be using cells C2:C1000 of your current workbook. =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),[Workbook1.xls]'sheet1'!$C$2:$C$100,0)) Regards Roger Govier spodosaurus wrote: spodosaurus wrote: Roger Govier wrote: Hi I think your second Workbook reference is superfluous. Try =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) Gives me a circular error, so it appears I might need it :-/ I'm wondering if I have the workbook references on the correct sides of all the parentheses...? Okay, starting from he =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) I think the MATCH afunction might actually need extra references to the workbook for its second argument: MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0) This tells it to look in Workbook1.xls for the first argument, but then perhaps it's looking to the workbook that it's in (Workbook19.xls) for the $C$2:$C$100 value? I'm posting this from a separate computer because the one that I'm working on is not networked at present, so bare with me while I speculate then travel back and forth to test things. Regards Roger Govier spodosaurus wrote: Roger Govier wrote: Hi With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier It almost works! Excel gives me an error with the second workbook reference in this formula: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) The first and thrid references to Workbook1.xls seem to be okay. I've even tried adding extra parentheses around the second reference, like this: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0)) again, without success :-( |
#12
|
|||
|
|||
Why not let XL create the links (paths) for you?
Open all the WBs and start the formula from scratch. =INDEX( Now, navigate to the WB in question, click in the starting cell, drag to the ending cell, then enter a comma in the formula *in the formula bar*. (You'll see that XL has inserted the actual path for you.) Now, continue typing in the formula bar: MATCH(MAX( And continue on ... navigating to the WBs and cells in question, and then typing in the punctuation and functions. When finished, hit <Enter, and you should have your properly configured formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "spodosaurus" wrote in message ... spodosaurus wrote: Roger Govier wrote: Hi I think your second Workbook reference is superfluous. Try =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1' !$C$2:$C$100),$C$2:$C$100,0)) Gives me a circular error, so it appears I might need it :-/ I'm wondering if I have the workbook references on the correct sides of all the parentheses...? Okay, starting from he =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX ([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) I think the MATCH afunction might actually need extra references to the workbook for its second argument: MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2: $C$100,0) This tells it to look in Workbook1.xls for the first argument, but then perhaps it's looking to the workbook that it's in (Workbook19.xls) for the $C$2:$C$100 value? I'm posting this from a separate computer because the one that I'm working on is not networked at present, so bare with me while I speculate then travel back and forth to test things. Regards Roger Govier spodosaurus wrote: Roger Govier wrote: Hi With your workbooks, you need to put the workbook name inside [ ] square brackets, then refer to the sheet name before the cell reference. [Workbook1.xls]Sheet1!$A$2:$A$100 If they are all in the same subdirectory (or folder) as you are working with your summary, the above will suffice. If not then you will need [C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100 replacing the C:\Excel\My work\Workbook1.xls with your relevant path and filename. Regards Roger Govier It almost works! Excel gives me an error with the second workbook reference in this formula: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX ([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0)) The first and thrid references to Workbook1.xls seem to be okay. I've even tried adding extra parentheses around the second reference, like this: =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MA X([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0)) again, without success :-( -- spammage trappage: remove the underscores to reply I'm going to die rather sooner than I'd like. I tried to protect my neighbours from crime, and became the victim of it. Complications in hospital following this resulted in a serious illness. I now need a bone marrow transplant. Many people around the world are waiting for a marrow transplant, too. Please volunteer to be a marrow donor: http://www.abmdr.org.au/ http://www.marrow.org/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Please - Match & Index Functions (I hope)! | Excel Discussion (Misc queries) | |||
Pivot Table Customize functions in the Data Field | Excel Discussion (Misc queries) |