Linking Excel Workbook

Put the date of the file you want in a cell, let's say A1. Then use INDIRECT to reference any cell in your reference file. You may have to set up each link manually one last time, but then any time you update the date, the links will change to the new file.

Please Login or Register  to view this content.

The example refers to Sheet1!C9 in the reference file. Of course, you need to have the exact same format for your reference files ...

Thanks Bentleybob for your feedback.

I was further researching this on the web and I came across a video by Mr. Excel discussing the same issue i raised here. Link: http://www.youtube.com/watch?v=crbGfqEorXQ

I tried to recreate the file as it applies to me but the end result is still giving me a #ref! error message. Attached are the files I am testing this formula on. I downloaded the PULL function and added it to the VBA module. Link :http://groups.google.com/group/micro...49f6c074a3adfd

The file labeled "050911 combined...xlsx" is the source file and the "summary" file is the destination file where the formula will be stored.

I hope you can help me on this.

Thanks.

Put the following in I2 and copy across and down:

Please Login or Register  to view this content.

This is great BentleyBob! Thank you so much. Is there a way to make this formula return the data from the source file without opening it?

Unfortunately, no, INDIRECT is a volatile function. But you can load up the file and then immediately close it. You can even have VBA code that opens and closes the file so you don't have to.

Hello BentelyBob,

I was able to take the HLOOKUP formula (without INDIRECT) and incorported it with PULL function to grab the values from a closed workbook!

Here is the formula I used:

Please Login or Register  to view this content.

I would not have done this without your help, so thank you so much!

Cheers!