Reference formula with variable sheet name and relative cell

[Cheryl4g]

I need to reference data from one sheet to the other and the sheet name can change and the cell needs to be relative so if a line is added in the other sheet the reference is not lost.
I tried using Indirect with the name of the sheet in cell C9, which changes,
=INDIRECT("'"&$C$9&"'!"&"D23")
but if a row was added in the sheet, D23 needs to change to D24 to pull the right data but it doesn't.
I tried Concatenate but I can't get the syntax right.

Any help would be greatly appreciated, since this is the last problem I need to fix in a file they have to have asap. UUghghh

 

[Dryver14]

if on sheet1 I input =sheet2!C9

it always refers to that cell, even if i insert rows on sheet2 or rename sheet2

are you overcomplicating this?

 

[Cheryl4g]

But I need to have the sheet name change.
If cell C9=2011 I need it to find cell D25 on sheet named "2011"
If C9= 2012 I need it to find cell D25 on sheet named "2012" or D26 if they added a row.

 

[Dryver14]

Ah So Batman

so the sheet name of the reference cell dosent change you want to look at a different sheet entirely!

 

[Cheryl4g]

Well kind of....
The Sheet names get renamed depending on the year.
If it's 2011 the sheets are named 2011, 2012, 2013
and if a cell in the Summary tab is changed from 2011 to 2012 the tabs are renamed to 2012, 2013, 2014.
Hopefully that makes sense?

The order of the sheets are always the same so if I can reference the cell by sheet order instead of sheet name "2011" then it might be easier?
But I can't figure out the syntax for the formula.

 

[Dryver14]

ok its long winded but it works

on the front page lets say you are inputting

2011,2012 or 2013 in cell C9


in C10 or where ever you want the return value

=IF(C9=2011,sheet2011!D25,If(C9=2012,sheet2012!D25,IF(C9=2013,sheet2013!D25,"")))

You can select the relevant returns by clicking on the cell to return the value.

If you insert a line on any of the sheets you will still get the right return value

 

[Cheryl4g]

But that will only work for those years. They want a file that is dynamic so they don't have to manually update any year information when a new year comes. Just adjust the year in one box and it all updates.

 

[Dryver14]

if you are loooking at sheet2011!D25 in this reference and it returns a 4


if you add rows to the sheet you will still get a 4

if you change the sheetname tab to 2014 and and some rows you will get a 4

have a little play with it just in a cell on sheet1 type= and then click a cell on the next sheet and press enter, then put a number in the cell on sheet 2 insert some rows etc

 

[Haseeb Avarakkan]

if a row was added in the sheet, D23 needs to change to D24 to pull the right data but it doesn't...........

Is this possible to use INDEX/MATCH or VLOOKUP?

Do you have any particular values in col B,C in all sheets?

Is D23 always be the last cell in each sheet?