Named Range in a External File Formula

W

WAstarita

I have a simple VBA program that when run, looks at a date in a
particular cell and updates 6 named ranges with specific file names it
uses based off of that days date.

I have a folder for every day of the week and inside there are 6
exactly named files:

"Tech Name - Service Log - Date.xls" (Date is in mm-dd-yyyy format)

The named ranges are "TechName1File, TechName2File, ect...."

How do I make this work:

After successfully running the macro to fill the named ranges, the
named ranges will look like this:
Named Range:
TechName1File =
\\Server\Folder\Folder\<Year>\<Month>\<Date>\FileName.xls

In Cell A1...

=TechName1File!Tech1Name

Cell A1 should display the value of Named Range Tech1Name from the file
referenced in the named range TechName1File

Problem is, it doesn't display anything but a #NAME?. If I go to edit
the cell, it will give me the Open dialog to choose a file manually,
which works fine but totally defeats the purpose.

Any ideas?

Thanks in advance
 
D

Dave Peterson

If the other file is open, then you could use something like:

=INDIRECT("'" & techname1file & "'!tech1name")

But =indirect() won't work with closed files.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip
 
W

WAstarita

I'm trying not to get that fancy with it. Again, if I type in the
actual path in the cell forumla, it works fine, wheather the book is
open or closed, its replacing the path with the Named Range which is
tricky
 
Top