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.

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you click Replace All?

 

[jularobert]

Hi Andrew
The issue is that for every cell (and I have 264) the find & replace (or replace all) takes me to the update values pop-up window. Excel is asking for the file location where the reference points, then is asking to chose the particular worksheet. Although I use "replace all" it only does it for one cell then the update values window comes up again for the next cell. Agreed, the replace all should replace all but it's not happening. Any ideas how to fix?
Thank you for all your help.

 

[Andrew Poulsom]

Why doesn't the worksheet exist in the workbook that contains the formulas?

 

[jularobert]

It does. That's why I don't understand why replace all is not working.
Basically the workbook has a sheet name '2013 new' and one named 'issues'. The formula in 'issues' point to sheet '2013 new'. As I need the same set-up for 2014, I've copied the two worksheets and renamed them accordingly; the problem is that I am unable the mass change the worksheet reference for all the formulas in the new worksheet 'issues 201' to point to to '2014'.

 

[Andrew Poulsom]

You must have the With and What wrong.

 

[jularobert]

No, sorry. All correct there. As soon as I chose replace Excel opens the "update values" window rather than just replacing the text in the formula.

I wonder if this could be solved in VBA?

Thank you

 

[Andrew Poulsom]

If you are getting the Update Values dialog the worksheet doesn't exist. Please post an existing formula and what you want it to be.

 

[jularobert]

This is the issue. Excel does not see the worksheet for some reason. But after going through Update Values dialog it does update the formula then moves to the next cell and the Update Value windows comes up again.

current formula:
C3=IF('2013 new'!$G4="", "", IF(COUNTIF('2013 new'!$G4,"*No*"),"No","Yes"))
would like to change to:
C3=IF('2014'!$G4="", "", IF(COUNTIF('2014'!$G4,"*No*"),"No","Yes"))

 

[Andrew Poulsom]

Replacing "2013 new" with "2014" (no quotes) worked for me (Excel 2010).