Relative vs absolute addressing with sheets

[paulkinzelman]

I've got a spreadsheet that will have one tab/sheet per year, called (coincidentally 2012, 2013, 2014, etc.

I'd like the 2013 sheet to take values from the 2012 sheet, and the 2014 sheet to take values from the 2013 sheet, etc.

So I created the 2012 and 2013 sheets, and on the 2013 sheet I refer to the 2012 sheet like this: '2012'!Q18 or in Libreoffice: '2012'.Q18

However, I'd like to be able to create the 2014 sheet by duplicating the 2013 sheet, renaming it to 2014, and have the above formula (via relative addressing rules) be changed to say '2013'!Q18 on the 2014 sheet.

But in fact, the formulas on the 2014 page will still refer to 2012 instead of 2013.

Is there some way to cause it to use relative addressing (like using $X$Y for cells on a sheet)? Or any other solution to the problem? What do people do who need to do this?

 

[Smitty]

You're looking for INDIRECT:

=INDIRECT(A1&"!Q18")

Where A1 holds the year.

HTH,

 

[paulkinzelman]

Thank you! That looks like it'll work!