The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

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

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

Dan Deschambault wrote:

I have a spreadsheet which pulls 3 sets of data from approximately 40
different files.

I need to update the referred path of each cell every week.

Is there a way to make "partial" path names in seperate cells and
essentially create the total path by referring to those cells?

Example:

A1: "'c:\"
A2: "Excel\"
A3: "Project\"
A4: "[Test.xls]Sheet1'!$B$2"

Then refer to A1, A2, A3, A4 to create a cell reference to
"'c:\Excel\Project\[Test.xls]Sheet1'!$B$2"

This way, I could simply change the data in a few cells and and update my
whole spreadsheet.

-Working on Excel 2003-

Thanks for any input!

Dan

--

Dave Peterson