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?