Can I add a formula to a link?

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.

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

"Kenton_SJ" wrote:

> 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.

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" <Kenton_SJ@discussions.microsoft.com> wrote in message
news:8ED6F52B-5644-4783-9BD0-2F7479D3BA3F@microsoft.com...
> 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.

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.

"RagDyer" wrote:

> 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" <Kenton_SJ@discussions.microsoft.com> wrote in message
> news:8ED6F52B-5644-4783-9BD0-2F7479D3BA3F@microsoft.com...
> > 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.
>
>

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!
==============================================


"Kenton_SJ" <KentonSJ@discussions.microsoft.com> wrote in message
news:5B47E8B4-460A-43CC-9815-E776AEAADDB7@microsoft.com...
> 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.
>
> "RagDyer" wrote:
>
> > 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" <Kenton_SJ@discussions.microsoft.com> wrote in message
> > news:8ED6F52B-5644-4783-9BD0-2F7479D3BA3F@microsoft.com...
> > > 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.
> >
> >