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 :-(
|