I'm trying to use a vlookup formula and refer to a range name located in an
external spreadsheet but it is not working.

Formula is as follows:

=IF(ISERROR(VLOOKUP($B13,'S:\Reporting\Monthly\[AVNWcategories.xls]fata',8,FALSE)),"",VLOOKUP($B13,'S:\Reporting\Mont hly\[AVNWcategories.xls]fata',8,FALSE))

The formula is 1st checking to see if the text in cell B13 is within the
"fata" range in the external spreadsheet. If the text in B13 is not found,
there would be an error. If there is an error, my IF statement should return
a blank cell indicated by the double quotes (""). If the formula finds the
text in cell B13, the value should be picked up from column 8 within the
"fata" range.

Somehow this formula is not valid but I'm not sure where?

I feel very stupid right now so any help would be great.

Thanks

Just worked on this today. You need a ! after the ' around the workbook name
and before the range name. 'workbook.xls'!rangename should do it.

HTH
Jim

"cgsteel" wrote:

I'm trying to use a vlookup formula and refer to a range name located in an
external spreadsheet but it is not working.

Formula is as follows:

=IF(ISERROR(VLOOKUP($B13,'S:\Reporting\Monthly\[AVNWcategories.xls]fata',8,FALSE)),"",VLOOKUP($B13,'S:\Reporting\Mont hly\[AVNWcategories.xls]fata',8,FALSE))

The formula is 1st checking to see if the text in cell B13 is within the
"fata" range in the external spreadsheet. If the text in B13 is not found,
there would be an error. If there is an error, my IF statement should return
a blank cell indicated by the double quotes (""). If the formula finds the
text in cell B13, the value should be picked up from column 8 within the
"fata" range.

Somehow this formula is not valid but I'm not sure where?

I feel very stupid right now so any help would be great.

Thanks