Change just the sheet name all at once in all cells containing formulas

[des333]

I have a cashflow summary sheet called YE2011 divided into months (columns) and various income/exp (rows) that takes data from another sheet called Data10-11. E.g. ='Data 09-10'!B20. So there's lots of cells copying from the Data sheet.

I now want to rename the cashflow summary sheet to YE2012 (for the current year) that will take data from a new sheet called Data11-12. I just require all the cell formulas to see the new sheet and not the old one. That is, I now need the formula to read 'Data 10-11'!B20.

 

[Comfy]

Use Find and Replace in Excel make sure the options are set to look in formulas

Find Data 09-10
Replace Data 10-11

 

[des333]

Excellent! Thnx very much for that easy (when you know how) remedy.

 

[nightcrawler23]

Or u can use INDIRECT to get the sheet name from another fixed cell. then every time u need just change the sheet name in that cell. U can also have dropdown in the cell if u need to say generate a report from multiple data sheets..

 

[des333]

I have also tried to use INDIRECT -but cant fathom how it would work with my particular situation. Could you pls assist with a step-by-step guide?