Lookup a range in another file?

[Joe]

The current file has multiple tabs with many lookup formulas and the
performance is slowing down considerably.

I have 4 main tabs which contain the source data name ranges. Can I move
these tabs to a separate file and have the lookup formulas in the original
file reference the ranges in this new file?

Any help would be appreciated.

Thanks, Joe

 

[T. Valko]

A lot of good info on efficiency here:

http://www.decisionmodels.com/

 

[Joe]

Thanks for the useful link.

However can a vlookup formula reference a named range in another workbook?

 

[ward376]

Yes. Use the workbook name and the range name for the table array:

=VLOOKUP(A2,'WorkbookName'!RangeName,2,FALSE)

This isn't going to improve performance though.

Cliff Edwards

 

[T. Valko]

can a vlookup formula reference a named range in another workbook?

Yes. You have to include the path if the source file will be closed:

Table = named range in XXX.xls
Refers to: Sheet1!A14

=SUM('C:\TV\xxx.xls'!table)

I don't think this will help from an efficiency standpoint, though. The
formula still has to calculate and now it has to link to an external source.

 

[ShaneDevenshire]

Hi Joe,

That is not going to improve efficiency, in fact its going to slow things
down in most cases.