I am building a template in an Excel worksheet. Users will use this template
to create one new record for every employee we have. There is a drop-down
list in the template where users can select which employee to build the
record for.

Once users have made the selection, a lookup formula finds that employee's
ID# in another cell.

Then, four of the cells in the template have to be updated by referencing an
old worksheet for that particular employee. The old worksheets are in a
separate folder. They are each named in this format: employee ID#Date.xls,
where the date is constant for all. (For example, A103B806.xls, where
"A103B" is the employee ID# and "806" is the date reference.)

I want to find a way to avoid making each user create links to the old file
for each individual. I can string together the ID # and date to re-create
the file name, and can also string together the rest of the link to the
folder where the other files are stored (using the CONCATENATE function).

How can I make the resulting text, which looks exactly like a formula but is
in text format, into a formula that automatically calculates (no need for
each user to do anything to make it calculate)?

Thank you.