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