Embedding the value of a cell in a formula

[Len]

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?

 

[Jacob Skaria]

Correction
A2 = 2009

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

If this post helps click Yes

 

[Dave Peterson]

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.