Referring to cells on other sheets using formula

[ClaireS]

I am trying to automate the way I refer to cells on other worksheets, by
working out the name of the worksheet using a formula and then referring to
this formula. For example: the sheet I want to refer has a name of the form:
"Year x months ahead". Year, and x can vary in the worksheet doing the
referring, so for example: in one sheet I need to refer to worksheet "2006 6
months ahead", and in that sheet "2006" and "6" are variables which I can use
to get the sheet name to be referred to.
I can set up the sheet name I require using the & function, but then excel
doesn't like it I try and refer to the other sheet using this equation.
I hope that makes sense - its quite difficult to explain my problem!

 

[Jacob Skaria]

With the below values in your active sheet
A1 = 2006
A2 = 6

the below formula will refer to sheet '2006 6 months ahead' cell A1

=INDIRECT("'" & A1& " " & A2 & " months ahead'!A1")


If this post helps click Yes

 

[ClaireS]

Jacob

This partially works, but I now want to use this cell reference as the first
argument in the OFFSET function. Excel doesn't like this - it picks this up
as text rather than a cell ref in another sheet. Any ideas?

 

[Jacob Skaria]

=OFFSET(INDIRECT("'" & A1& " " & A2 & " months ahead'!A1"),1,1)

refers to sheet '2006 6 months ahead' cell B2

If this post helps click Yes