I would like to have the contents of a cell be part of a formula. For example:

In one cell I have the year: 2009

In the other a formula references another workbook: =C:/.../Ad Sales 2009!A1

I would like to be able to change the first cell to year 2010 and have the
formula automatically change with it to =C:/.../Ad Sales 2010!A1

Is this possible?

With the year in A2; try replacing the file name with the below
=INDIRECT("C:/.../Ad Sales " & A2 & "!A1")

If this post helps click Yes
---------------
Jacob Skaria


"Len" wrote:

I would like to have the contents of a cell be part of a formula. For example:

In one cell I have the year: 2009

In the other a formula references another workbook: =C:/.../Ad Sales 2009!A1

I would like to be able to change the first cell to year 2010 and have the
formula automatically change with it to =C:/.../Ad Sales 2010!A1

Is this possible?

Yes, with the INDIRECT function. Let's say your year cell is B2. Formula then
becomes
=INDIRECT("C:/.../Ad Sales " & B2 & "!A1")

Note that you can join text and cells references together using the
ampersand. The spaces are not necessary, I just put them in there for clarity.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Len" wrote:

I would like to have the contents of a cell be part of a formula. For example:

In one cell I have the year: 2009

In the other a formula references another workbook: =C:/.../Ad Sales 2009!A1

I would like to be able to change the first cell to year 2010 and have the
formula automatically change with it to =C:/.../Ad Sales 2010!A1

Is this possible?

Say we put the workbook name in H6 and in another cell:

=INDIRECT("'C:\test folder\[" & H6 & ".xls]Sheet2'!$F$3")

so if H6 contains 2009, the formula will return the value from 2009.xls

Note that we can specify sheet and cells as well. The limitation is that
the workbook specified in H6 must be open.
--
Gary''s Student - gsnu200857


"Len" wrote:

I would like to have the contents of a cell be part of a formula. For example:

In one cell I have the year: 2009

In the other a formula references another workbook: =C:/.../Ad Sales 2009!A1

I would like to be able to change the first cell to year 2010 and have the
formula automatically change with it to =C:/.../Ad Sales 2010!A1

Is this possible?

Correction
A2 = 2009

=INDIRECT("'c:\[xlsfilename.xls]Ad Sales " & A2 &"'!A1")

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With the year in A2; try replacing the file name with the below
=INDIRECT("C:/.../Ad Sales " & A2 & "!A1")

If this post helps click Yes
---------------
Jacob Skaria


"Len" wrote:

I would like to have the contents of a cell be part of a formula. For example:

In one cell I have the year: 2009

In the other a formula references another workbook: =C:/.../Ad Sales 2009!A1

I would like to be able to change the first cell to year 2010 and have the
formula automatically change with it to =C:/.../Ad Sales 2010!A1

Is this possible?

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.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

Len wrote:

I would like to have the contents of a cell be part of a formula. For example:

In one cell I have the year: 2009

In the other a formula references another workbook: =C:/.../Ad Sales 2009!A1

I would like to be able to change the first cell to year 2010 and have the
formula automatically change with it to =C:/.../Ad Sales 2010!A1

Is this possible?

--

Dave Peterson