MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Linking or retrieving data from one workbook to another


Posted by Jay on February 01, 2002 9:41 PM

Hi
Help, I am having a syntax problem. I don't know if I have the proper dots and dashes on thi formula.

=IF(ISNA(VLOOKUP("[Book21.xls]""Sheet3"!$A3,$V$2:$Z$4,2,FALSE)),"",VLOOKUP("[Book21.xls]""Sheet3"!$A3,$V$2:$Z$4,2,FALSE))

I have this formula in a workbook named Equipment.xls which means nothing but it let's you know I am trying to link two workbooks. Without the ["Book21.xls"]""Sheet3"! This formula works in workbook Book21.xls Thanks again for any help.


Posted by Yogi Anand on February 01, 2002 10:38 PM

Hi Jay:
You are using several functions, such as IF, ISNA, and VLOOKUP for looking up data in a table -- so here in the first part of the formula, 1) your lookup value is in cell A3, your lookup range is V2:Z4, you are looking up the value in col 2, and you are looking up for an exact match -- now all of this is in Sheet3 of Workbook21.
It looks like in your formula you have some erroneous quotes -- the two possible situations are -- if the workbook is open, your formula should look like
=IF(ISNA(VLOOKUP([Book21.xls]Sheet3!$A3,$V$2....

If the workbook is closed, your formula should look like
=IF(ISNA(VLOOKUP('drive\folder\[BOOK21.xls]Sheet3'!$A3,$V$2 ....

So, as you can see, you have some basic syntax error in your formula, so first you should look at this aspect -- I can not tell you more from the limited description you have provided here in this post.

HTH

Yogi Anand
ANAND Enterprises (broken link)