VLookup - fails to return data

  • Thread starter Thread starter CarolA
  • Start date Start date
C

CarolA

Hi,

I have created a quote sheet that pulls through descriptions and costs
from 2 different price files - saved as seperate workbooks. These price
files are updated with new file names on a monthly basis - new
filenames so we have a record of previous costs. I have tried to
update my Jan files and the links no longer seem to pull through any
data and just display #N/A.

Code being used is:

=IF($L21="V",IF($B21>0,VLOOKUP($B21,'I:\Product
information\Software\Veritas\Price lists\Enterprise\[FulPriceList_UK
DEC03.xls]FullPriceList'!Print_Area,3,FALSE),""),IF($B21>0,VLOOKUP($B21,'I:\INTEL\Pricing\HP
Intel Pricelist.xls'!intel_prices,2,FALSE),""))

L21 = cell where if V is displayed it looks in one file, no v means use
other file.
B21 is part code that is used to search the price files.

Can anyone think why the link no longer pulls through when I change the
filenames and links?
 
I believe you need to review the VLOOKUP function. Further, if you want two
or more conditions to be TRUE to perform the VLOOKUP, use the AND function:
=IF(AND($L21="V",$B21>0),VLOOKUP(etc, etc.
 
...
...
. . . I have tried to
update my Jan files and the links no longer seem to pull through any
data and just display #N/A.

Code being used is:

=IF($L21="V",IF($B21>0,VLOOKUP($B21,
'I:\Product information\Software\Veritas\Price lists\Enterprise\
[FulPriceList_UK DEC03.xls]FullPriceList'!Print_Area,3,FALSE),""),
IF($B21>0,VLOOKUP($B21,
'I:\INTEL\Pricing\HP Intel Pricelist.xls'!intel_prices,2,FALSE),"")) ...
Can anyone think why the link no longer pulls through when I change the
filenames and links?
Click to expand...

Using Print_Area is dangerous because it's a range name used by Excel when you
select particular ranges on a worksheet rather than the entire used range of the
worksheet to print. I'd bet Print_Area changed. Either define a different name
referring to the desired range then change Print_Area to that range name or
replace Print_Area with the actual range address. As a check, what does

=COUNTA('I:\Product information\Software\Veritas\Price lists\Enterprise\
[FulPriceList_UK DEC03.xls]FullPriceList'!Print_Area)

return? If the result is unreasonably small, the problem would almost certainly
be a changed Print_Area definition. If it looks reasonable but is very large,
then you may just have to have that other file open in order to refer to it.
 
Back
Top