Using a cell’s information as a variable?

2

2304

I have a Master worksheet with that contains cell information of a
individual worksheet saved as a 4 digit employee number (2304.xls) fo
example.
='X:\Time_Sheets\EMPLOYEE\[2304.xls]Sheet1'!$E$41
I would want to replace with..
='X:\Time_Sheets\EMPLOYEE\[2261.xls]Sheet1'!$E$41
I have every row containing a different employee excel shee
information and each row has 20 columns of specific employe
information.
I know I can highlight a row and use the find and replace functio
(find 2304.xls and replace with 2261.xls) for example.
Can I create a cell on the master Sheet and type in what info I want t
force (string) into the pointer formulas. Example, I would type (2304
in the first cell of the column and it would fill in the followin
pointers. It would act somewhat as a find and replace. And I coul
easily replace or move employees to different Master sheets. Thanks fo
ANY help?
J
 
D

Dave Peterson

If you put the workbook name into another cell, you could use =indirect() to
build the reference to the other workbook.

The bad news is that =indirect() will return an error if that other workbook
isn't open.

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

Or you could continue using Edit|Replace to change the formulas.


I have a Master worksheet with that contains cell information of an
individual worksheet saved as a 4 digit employee number (2304.xls) for
example.
='X:\Time_Sheets\EMPLOYEE\[2304.xls]Sheet1'!$E$41
I would want to replace with..
='X:\Time_Sheets\EMPLOYEE\[2261.xls]Sheet1'!$E$41
I have every row containing a different employee excel sheet
information and each row has 20 columns of specific employee
information.
I know I can highlight a row and use the find and replace function
(find 2304.xls and replace with 2261.xls) for example.
Can I create a cell on the master Sheet and type in what info I want to
force (string) into the pointer formulas. Example, I would type (2304)
in the first cell of the column and it would fill in the following
pointers. It would act somewhat as a find and replace. And I could
easily replace or move employees to different Master sheets. Thanks for
ANY help?
JH
Click to expand...
 
Top