Automatically cell linking in new sheet.

[amruthubballi]

Hey Experts I need your help.

I have to create a financial statements for set of years. We know that closing balance of particular year becomes opening balances of next year, so we link the closing balance of last year to opening balance of new year in new sheet. When we are done with creating one year statement and while creating next year statements as most of cases we find all same entries so we copy the whole sheet and paste in new sheet. We create a 1st year statements and for 2nd year statements we link the closing balance of 1st year.... we create 3rd year statement by copying whole 2nd year statements, here the problem arises, when we copy sheet in opening balances the link we created refers to the closing balance of 1st year(not 2nd year statements). So I need some shortcut to link the closing balance of 2nd year. Is it possible to automatically link as I said?
Please note that closing are typed manually.

Example: (refer my attachment file: https://drive.google.com/file/d/0B8Q1PicvZuJnRDd3dlc3bmthdGs/view?usp=sharing)

We prepare the sheet 2012... we copy the sheet 2012 and rename it as 2013 and in opening cash balance(D6) of 2013 we linked as ='2012'!F15. Now we copy sheet 2013 and rename it as 2014 but in the sheet 2014, Cell:D6 reference will be of 2012 (i.e ='2012'!F15) I want it to be closing balance of 2013 (i.e Cell: ='2013'!F15) without updating manually.
please note "xxx" indicates different amount without linking cells.

Hope this much is sufficient to understand and solve so please someone help and reduce my work with just sharing of your knowledge.

 

[Chrisdontm]

You could use the INDIRECT formula and have it look at the year at the top of the table...

So all you would have to change is the year up top.

So for the formula in cell: D6

Try this: =INDIRECT("'"&RIGHT($C$2,4)-1&"'!F15",1)

In each of the other places just change the cell reference from F15 to F16, or F18 as needed.

 

[amruthubballi]

Thank you for your reply.... It helped to reduce the work to some extent.