lookup tables across two workbooks

  • Thread starter Thread starter darrellps
  • Start date Start date
D

darrellps

Hi
I have two workbooks to which i wish to do a lookup for.

The first wookbook contains totals via subtotals as below

A1 0741.702.113 B1 $250.00 C1 EXPENSES

A5 0745.753.112 B5 $2510.00 C5 BANK FEES

The other workbook contains a vlookup to get the dollar amount from
the first workbook and inserts into the second workbook.(column 2)

It appears it doesn't work because the first workbook is total made up
of subtotals with the other rows being hidden.

Do i have to use the vlookup combined with match etc?? or maybe the
format is the problem??

Thankyou in advance

Darrell
 
The hidden rows didn't affect my =vlookup().

If you're positive that there is a match, pick out the two cells that that
should match up and put something like this in an unused cell:

=a2=sheet2!a9

If it comes out True, then the values matched. If it evaluates as False, then
maybe you don't have what you expect in both cells. (extra
leading/trailing/embedded spaces???--numbers versus text (123 <> '123).)
 
And if none of these suggestions worked, maybe the OP could paste the formula
that didn't work into the followup message.
 
If you are refering to external links to other workbooks, the othe
workbook must be open for the formula to work. I'm assuming you
formula looks something like this:

=vlookup(a1,[workbook1]Sheet1!$a:$b,2,0)

That formula will work fine when the workbook1 is open. When workbook
is closed you may notice the formula change to something like this.

=vlookup(a1,'C:\mypath\[workbook1]Sheet1'!$a:$b,2,0)

If your formula resembles the second one, you may not have workbook
open, or you have chosen 'no' to update links to other workbooks.

One more way to check if your formula's are experiencing this is b
going to Edit>Links.

Hope that helps..

Ben Sullin
 
This type of =vlookup() formula has always worked for me with closed workbooks.

There are other functions that don't work so nicely, though (=indirect(),
=sumif() spring to mind).
If you are refering to external links to other workbooks, the other
workbook must be open for the formula to work. I'm assuming your
formula looks something like this:

=vlookup(a1,[workbook1]Sheet1!$a:$b,2,0)

That formula will work fine when the workbook1 is open. When workbook1
is closed you may notice the formula change to something like this.

=vlookup(a1,'C:\mypath\[workbook1]Sheet1'!$a:$b,2,0)

If your formula resembles the second one, you may not have workbook1
open, or you have chosen 'no' to update links to other workbooks.

One more way to check if your formula's are experiencing this is by
going to Edit>Links.

Hope that helps..

Ben Sullins
Click to expand...
 
Back
Top