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.

You would create a macro. Declare a constant to hold the name of the
directory/folder htat contains the old file, and a variable to hold the name
of the file you are looking for, read the emp ID into this variable, and
concatenate with the date ref. Say your emp ID is in B4, then something like
vFName = Range("B4") & "806". Say your lookup range is in the other file,
from A2:G250, A being the emp ID and C being the info you are looking for.

Where you want the VLOOKUP formula, enter your formula with
Range("D4").formula = "=VLOOKUP(B4,cDir &"\" & vFName & "!A2:G250,3,FALSE)"

Do the sqame for the other 3 columns, obviously chaging the offsets.
--
Hth

Kassie Kasselman


"klp2344" wrote:

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.