Worksheet reference syntax?
 

I have a workbook that contains several tabs named by years (eg Y2001,
Y2002, Y2003...).

Is there a syntax that would reference a cell whose worksheet is based
on the result of a formula? So, for example, if the formula resulted
in 2002, it would return the A1 value in the Y2002 worksheet instead
of any of the others

Something like this perhaps?

="Y"&formula_that_gives_a_year_value!A1

Worksheet reference syntax?
 

You need the INDIRECT function.
--
David Biddulph

"Matt" wrote in message
...

I have a workbook that contains several tabs named by years (eg Y2001,
Y2002, Y2003...).

Is there a syntax that would reference a cell whose worksheet is based
on the result of a formula? So, for example, if the formula resulted
in 2002, it would return the A1 value in the Y2002 worksheet instead
of any of the others

Something like this perhaps?

="Y"&formula_that_gives_a_year_value!A1

Worksheet reference syntax?
 

You need to do it like this:

=INDIRECT("'Y" & your_formula & "'!A1")

The apostrophes are in there in case you have spaces in the sheet
names.

Hope this helps.

Pete

On Apr 25, 1:02*pm, Matt wrote:
I have a workbook that contains several tabs named by years (eg Y2001,
Y2002, Y2003...).

Is there a syntax that would reference a cell whose worksheet is based
on the result of a formula? *So, for example, if the formula resulted
in 2002, it would return the A1 value in the Y2002 worksheet instead
of any of the others

Something like this perhaps?

="Y"&formula_that_gives_a_year_value!A1

Worksheet reference syntax?
 

Perfect, thanks!