How do I reference external data from a file, file name found in .

[Guest]

How do I reference data from a file when the file name is found in
destination workbook. i.e. I want to be able to have a cell whjere i type in
source desitnation file name and the workbook will reference that file in a
range

 

[Dave Peterson]

If you build your formula with the reference to that closed workbook, excel will
handle it nicely.

I like to open the other workbook.
Build the formula and use the mouse to point at that other range(?) in the other
workbook. Then when I close that other workbook, my formula's syntax will be
perfect--excel did all the real tough work!

If you put the workbook's name (including sheetname, range address) in other
cells and build your formula using these other cells, then the function you'd
want to use is =indirect(). But excel won't let you use this with closed
workbooks.

But Harlan Grove wrote a UDF that allows you to retrieve values from a closed
workbook when you build that string that points at the
drive/folder/filename/sheetname/range address.

http://www.google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm