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/