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/