I am working on a spreadsheet that compiles data from other spreadsheets. I
refer to data in the other spreadsheets with a formula like the one below:

=[filename.xls]Sheet1!$B$1

I would like to have a cell where I could change the filename, and have all
of cells that use that filename change. In other words, I would like to have
a variable in the formulas that refers to the file, like this:

=["variable".xls]Sheet1!$B$1

So I could specify that the variable is filename1, run my calculations, then
specify that the variable is filename2 and run the next set of calculations,
and so on.

Is this possible? If so, how do you do it?

Thanks for your help!

You'd want to use the =indirect() function, but that only works when the
"sending" workbook is open.

But 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

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

pdabill wrote:

I am working on a spreadsheet that compiles data from other spreadsheets. I
refer to data in the other spreadsheets with a formula like the one below:

=[filename.xls]Sheet1!$B$1

I would like to have a cell where I could change the filename, and have all
of cells that use that filename change. In other words, I would like to have
a variable in the formulas that refers to the file, like this:

=["variable".xls]Sheet1!$B$1

So I could specify that the variable is filename1, run my calculations, then
specify that the variable is filename2 and run the next set of calculations,
and so on.

Is this possible? If so, how do you do it?

Thanks for your help!

--

Dave Peterson