Referencing info in a closed XLS file

[Ody]

Hello all,

I'm attempting to create a roll up workbook that pulls information from other closed xls files. I'm basically just copying information from various points in other workbooks into another workbook.

I've read I can do this using a lookup formula. Is this possible? What's the easiest way for me to accomplish my goal?

Thanks!

 

[Ody]

bump

Any ideas? Thanks.

 

[MrKowz]

It can be done. Have you used LOOKUP functions (Lookup, VLookup, HLookup) before?

 

[Ody]

Yes, I'm familiar with lookups. I'm just not sure of the syntax.

 

[MrKowz]

Try creating the lookup like you would with any other lookup (selecting the range with your mouse). When you go to the other workbook, it will create the proper reference.

Basically, instead of referring to just the sheet or just the workbook name, it will reference the entire filepath as well.

For example:

If you want to do a VLOOKUP on A1:B10 in Sheet1 in a workbook named "Pricing.xls" that is in the directory "C:\Excel Files\Database", and you are comparing A1 to it, you would use

=VLOOKUP(A1,'C:\ExcelFiles\Database\[Pricing.xls]Sheet1'!A1:B10,2,0)

 

[Ody]

So in your example, even though the Pricing.xls file is closed, it'll still return the appropriate value?

I'll have to give that a shot. Thanks.