Can I add a formula to a link?

[Kenton_SJ]

Basically I have a yearly work book that maintains a record of my daily
activities. I'm am making a summary workbook that links to my yearly ones.
I'd like to be able to type in the year and then a formula changes the link
to the year number. My workbooks are called 2002, 2003, 2004.... I can get
it to link fine when I type in the filename, but I'm not sure if it is
possible to get a formula to do the same.

Thanks.

 

[bj]

check out the indirect() function.
I am not sure what you want to do with it, but think this will work.

 

[RagDyer]

If your original formulas might be:

=SUM([2002.xls]Sheet1!$B$1:$B$10)
=SUM([2003.xls]Sheet1!$B$1:$B$10)

Then use, say D1, to enter your WB name (2002, 2003, ... etc.)
And try this:

=SUM(INDIRECT("["&D1&".xls]sheet1!$B1:$B10"))

NOW, this *only* works when the other WBs are *open*.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Kenton_SJ]

Thanks RagDyer,

That worked sweet. Now I just have to try that INDIRECT.EXT function by
Xcell05 to see if it works with the sheets closed.

If your original formulas might be:

=SUM([2002.xls]Sheet1!$B$1:$B$10)
=SUM([2003.xls]Sheet1!$B$1:$B$10)

Then use, say D1, to enter your WB name (2002, 2003, ... etc.)
And try this:

=SUM(INDIRECT("["&D1&".xls]sheet1!$B1:$B10"))

NOW, this *only* works when the other WBs are *open*.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Basically I have a yearly work book that maintains a record of my daily
activities. I'm am making a summary workbook that links to my yearly ones.
I'd like to be able to type in the year and then a formula changes the link
to the year number. My workbooks are called 2002, 2003, 2004.... I can get
it to link fine when I type in the filename, but I'm not sure if it is
possible to get a formula to do the same.

Thanks.

 

[RagDyer]

I read somewhere that Laurent's add-in works well, *BUT* ... it *doesn't*
work with range references.

So, the formula I used as an example, might not work.

Harlan has come up with something to do a similar job, and supposedly works
with ranges, which he calls his "pull function".

Check it out here:

ftp://members.aol.com/hrlngrv/pull.zip

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks RagDyer,

That worked sweet. Now I just have to try that INDIRECT.EXT function by
Xcell05 to see if it works with the sheets closed.

If your original formulas might be:

=SUM([2002.xls]Sheet1!$B$1:$B$10)
=SUM([2003.xls]Sheet1!$B$1:$B$10)

Then use, say D1, to enter your WB name (2002, 2003, ... etc.)
And try this:

=SUM(INDIRECT("["&D1&".xls]sheet1!$B1:$B10"))

NOW, this *only* works when the other WBs are *open*.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Basically I have a yearly work book that maintains a record of my daily
activities. I'm am making a summary workbook that links to my yearly ones.
I'd like to be able to type in the year and then a formula changes the link
to the year number. My workbooks are called 2002, 2003, 2004.... I

can
get

it to link fine when I type in the filename, but I'm not sure if it is
possible to get a formula to do the same.

Thanks.