worksheets lookups
 

I have a vlookup that goes out to another file.

Because the other file has several worksheets it is only searching on the
worksheet that was selected is there any way to get the vlookup to look at
all the worksheets within a workbook.

Here is my statement.
=VLOOKUP(A19,'[New Monthly Invoices June 05.xls]Aug 05'!$D$1:$S$2,16)

It works fine, but only for the Aug 05 Sheet within the other workbooks,
please help me to search the entire workbook for this.

Thanks,

JJC

Hi!

The only "error proof" way to do this is to write a separate lookup formula
for each sheet of the other file.

There is a method that uses the INDIRECT function but this is not error
proof! It requires the other file be open but as soon as you close the other
file and the active file recalculates you get errors.

There is also a VBA utility out there but a lot of people who try it also
seem to have problems using it.

Biff

"JJC" wrote in message
...
I have a vlookup that goes out to another file.

Because the other file has several worksheets it is only searching on the
worksheet that was selected is there any way to get the vlookup to look at
all the worksheets within a workbook.

Here is my statement.
=VLOOKUP(A19,'[New Monthly Invoices June 05.xls]Aug 05'!$D$1:$S$2,16)

It works fine, but only for the Aug 05 Sheet within the other workbooks,
please help me to search the entire workbook for this.

Thanks,

JJC

JJC wrote...
I have a vlookup that goes out to another file.

Because the other file has several worksheets it is only searching on the
worksheet that was selected is there any way to get the vlookup to look at
all the worksheets within a workbook.

Here is my statement.
=VLOOKUP(A19,'[New Monthly Invoices June 05.xls]Aug 05'!$D$1:$S$2,16)

It works fine, but only for the Aug 05 Sheet within the other workbooks,
please help me to search the entire workbook for this.

If you're really using approximate matching (no 4th argument to
VLOOKUP), it's complicated. If this other worksheet has worksheet names
in MMM YY format, then use a defined name in the workbook containing
the formulas to generate a list of the worksheet names in the other
workbook IN REVERSE ORDER, e.g.,

WSLst referring to =TEXT(DATE(2005,13-ROW(INDIRECT("1:12")),1),"MMM
YY")

Then use array formulas like

=VLOOKUP(A19,INDIRECT("'[New Monthly Invoices June
05.xls]"&INDEX(WSLst,
MATCH(TRUE,COUNTIF(INDIRECT("'[New Monthly Invoices June 05.xls]"&WSLst
&"'!D1:D2"),"<="&A19)0,0))&"'!D1:S2"),16)

The workbook, New Monthly Invoices June 05.xls, *MUST* be open for this
to work.

The workbook, New Monthly Invoices June 05.xls, *MUST* be open for this
to work.

And the formulas will crash as soon as the other workbook is closed and a
calc occurs in the active wb!

Users should be made aware of this significant drawback!

Biff

"Harlan Grove" wrote in message
oups.com...
JJC wrote...
I have a vlookup that goes out to another file.

Because the other file has several worksheets it is only searching on the
worksheet that was selected is there any way to get the vlookup to look at
all the worksheets within a workbook.

Here is my statement.
=VLOOKUP(A19,'[New Monthly Invoices June 05.xls]Aug 05'!$D$1:$S$2,16)

It works fine, but only for the Aug 05 Sheet within the other workbooks,
please help me to search the entire workbook for this.

If you're really using approximate matching (no 4th argument to
VLOOKUP), it's complicated. If this other worksheet has worksheet names
in MMM YY format, then use a defined name in the workbook containing
the formulas to generate a list of the worksheet names in the other
workbook IN REVERSE ORDER, e.g.,

WSLst referring to =TEXT(DATE(2005,13-ROW(INDIRECT("1:12")),1),"MMM
YY")

Then use array formulas like

=VLOOKUP(A19,INDIRECT("'[New Monthly Invoices June
05.xls]"&INDEX(WSLst,
MATCH(TRUE,COUNTIF(INDIRECT("'[New Monthly Invoices June 05.xls]"&WSLst
&"'!D1:D2"),"<="&A19)0,0))&"'!D1:S2"),16)

The workbook, New Monthly Invoices June 05.xls, *MUST* be open for this
to work.

Biff wrote...
The workbook, New Monthly Invoices June 05.xls, *MUST* be open for this
to work.

And the formulas will crash as soon as the other workbook is closed and a
calc occurs in the active wb!

Users should be made aware of this significant drawback!
....

In Excel 2002, if the other workbook isn't open, my formula returns
#REF!. While I wasn't explicit about how the formula wouldn't work, I
was clear than it wouldn't if the other file weren't open.

By 'the formulas will crash', did you mean something other than
returning a #REF! error?

By 'the formulas will crash', did you mean something other than
returning a #REF! error?

No. I consider #REF! a "crash".

Biff

"Harlan Grove" wrote in message
oups.com...
Biff wrote...
The workbook, New Monthly Invoices June 05.xls, *MUST* be open for this
to work.

And the formulas will crash as soon as the other workbook is closed and a
calc occurs in the active wb!

Users should be made aware of this significant drawback!
...

In Excel 2002, if the other workbook isn't open, my formula returns
#REF!. While I wasn't explicit about how the formula wouldn't work, I
was clear than it wouldn't if the other file weren't open.

By 'the formulas will crash', did you mean something other than
returning a #REF! error?

Biff wrote...
By 'the formulas will crash', did you mean something other than
returning a #REF! error?

No. I consider #REF! a "crash".
....

That's nice. I consider an application error leading to unrecoverable
data loss a crash. #REF! is just an error message, like #VALUE! or
#NAME?.

So what wasn't sufficient about my caveat:

The workbook, New Monthly Invoices June 05.xls, *MUST* be open for this
to work.

?