Change sheet reference in multiple cells (formulas)

[jularobert]

I have a worksheet ("Issues") with 264 cells, all formulated to point to a sheet named "2013new".
i.e. C3=IF('2013 new'!$G4="", "", IF(COUNTIF('2013 new'!$G4,"*No*"),"No","Yes"))
D3=IF('2013 new'!$G5="", "", IF(COUNTIF('2013 new'!$G5,"*No*"),"No","Yes"))

I have created a copy of the "Issues" worksheet and I would like all the formulas to point to a new sheet called 2014.

Basically I am trying to replace all the references in the 264 cells (all the formulas) from '2013new' to '2014' in one go (en mass).
Can this be achieved? The Replace under Find&Select menu allows only for one cell at the time. (I am using Excel 2010)

Thanking you in advance.

 

[jularobert]

No, sorry. It doesn't work. It's maddening... , not sure what to do..short of re-writing each formula individually.

 

[Andrew Poulsom]

Check that your sheet 2014 doesn't have a trailing space in the name. Enter a formula by pointing at it and look carefully.

 

[jularobert]

Hi Andrew. There was no trailing space, but your idea proved right. I just renamed the worksheet (2014) to something else and it worked like a charm. Sorry for all the inconvenience and Happy Thanksgiving.

One more question on the issue, if the cells that contain the formulas are also hypelinked to sheet '2013' can you mass edit the hyperlinks to point to sheet '2014' instead of '2013'?
Again, thank you for all your help.

 

[Andrew Poulsom]

How can a cell contain a formula and a hyperlink without using the HYPERLINK function?

 

[jularobert]

Hi Andrew,
I've used hyperlink from right click menu.

 

[Andrew Poulsom]

If you use the HYPERLINK function you could use Replace.

 

[jularobert]

Thank you for your help and patience.