Excel Help Please!!! Making Duplicate Spreadsheets

[jameshsheet1]

Hi,

This is a difficult one to explain, but I'll try my best... I'm sure that the fix is very easy - I'm just not that great with IT!

I have 2 spreadsheets (files) Let's call them A2013 and B2013
These spreadsheets talk to each other (B2013 picks up data that is entered into A2013)

I want to make 2 new sheets for next year (2014) that are exactly the same. Let's call them A2014 and B2014.

How do I make duplicates from A2013 and B2013 so that A2014 and B2014 talk to each other in the same way? i.e. so that the formulas copy over but update to recognize the new 2014 sheet.

I hope that this makes sense.

Thank you very much for your help!

James

[Bob Phillips]

Use INDIRECT

For example

A2013 cell A5 - =INDIRECT("B"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+2,99)&"!A5")

The MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+2,99) gets the year number from the sheet name
Then append !cell to it
And pass the concatenated string to INDIRECT which indirects into that cell.