Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
spodosaurus
 
Posts: n/a
Default 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   Report Post  
spodosaurus
 
Posts: n/a
Default

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   Report Post  
Tom Ogilvy
 
Posts: n/a
Default

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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
spodosaurus
 
Posts: n/a
Default

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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
spodosaurus
 
Posts: n/a
Default

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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
spodosaurus
 
Posts: n/a
Default

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   Report Post  
spodosaurus
 
Posts: n/a
Default

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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help Please - Match & Index Functions (I hope)! baz Excel Discussion (Misc queries) 0 September 2nd 05 02:42 PM
Pivot Table Customize functions in the Data Field PSKelligan Excel Discussion (Misc queries) 2 January 4th 05 07:51 PM


All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"