Cell Refrences

[lj]

Here is my question. I would like to perform a dynamic file reference.
Let me explain. I have a spreadsheet that make computations based on
data in a file called "Week1.xls". When the week 1 is over, I want
to change my spreadsheet so that it pulls data the file
"Week2.xls". My problem is that this involves a bunch of find and
replaces, takes time, and is a generally annoying.

Is there a way that I can instead have my spreadsheet pull data from
the file "Week[B2].xls" where cell B2 contains the number
associated with the week I wish to reference?

That way when I want to populate the spreadsheet using the data from
week1.xls, I just change the value in cell B2 to be "1". When I
want to populate the spreadsheet using the data from week2.xls, I just
change the value in cell B2 to be "2". Thanks for your help!

 

[Barb Reinhardt]

GIve us an example of a couple of the references you are using now and we
can tell you how to change it.

 

[lj]

Here is an example below. I would like to be able to change the link
to link to the same cell in a different file named (week 2.xls) instead
of week 1. Is there any way to do this?

[Week1.xls]data!C4

 

[Bob Phillips]

Write the example out in text please. attachments seem to get blocked here
now, and were frowned upon beforehand.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[lj]

Uh, =[Week1.xls]data!C4 is my example, it's not an attachment. I want
to find out if i can change the file name by linking to another cell in
the spreadsheet and changing that cell instead of doing a find and
replace.

 

[Harlan Grove]

lj wrote...
....

Is there a way that I can instead have my spreadsheet pull data from
the file "Week[B2].xls" where cell B2 contains the number
associated with the week I wish to reference?

....

If that other file would also be open, you could use INDIRECT.

=INDIRECT("'[Week"&B2&".xls]Sheet1'!A1")

which is effectively an absolute reference to cell A1 in worksheet
Sheet1 in file Week#.xls where # is replaced by the value of cell B2.
If you want relative referencing, use

=INDIRECT("'[Week"&B2&".xls]Sheet1'!"&CELL("Address",A1))

If the other file could be closed, see the following article in the
archives.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).