How do I remove a portion of an IF/Then formula globally?

[Roinda22]

Hello, and thanks in advance for helping!

I have a formula,

=IF(YEAR(CC$6)=2011,'FTE by FA# Budget '!CD16*$G16*CC$9*$I16/1000,IF(YEAR(CC$6)=2012,'FTE by FA# Budget '!CD16*$G16*CC$9*$H16*$I16/1000,IF(YEAR(CC$6)=2013,'FTE by FA# Budget '!CD16*$G16*CC$9*$H16*$H16*$I16/1000,IF(YEAR(CC$6)=2014,'FTE by FA# Budget '!CD16*$G16*CC$9*$H16*$H16*$H16*$I16/1000,IF(YEAR(CC$6)=2004,'FTE by FA# Budget '!CD16*$G16*CC$9*$H16*$H16*$H16*$H16*$I16/1000)))))

that I need to remove the last portion

,IF(YEAR(CC$6)=2004,'FTE by FA# Budget '!CD16*$G16*CC$9*$H16*$H16*$H16*$H16*$I16/1000)

globally. I do not know how to write a find/replace for this because the cell references change for each cell's formula. Such as

,IF(YEAR(CB$6)=2004,'FTE by FA# Budget '!CC27*$G27*CB$9*$H27*$H27*$H27*$H27*$I27/1000)


Any help is greatly appreciated.

Thank you!

 

[Gerald Higgins]

Perhaps use a wildcard character, such as

FIND
If(year(CB$6)=2004*

 

[Roinda22]

Would this only search (CB$6) though? The problem is this cell reference changes, such as (CC$6). I am looking for a very easy way to just find and remove that entire last if/then statement, which has both static text and dynamic cell references that change for each cell/formula. Can wildcard do that?

Thanks for the reply!!!

 

[Rick Rothstein]

Unless I am missing something, why not just edit the first formula to look like you want and then just copy it down (across?) through all the cells?

 

[Roinda22]

I could, but I would prefer not to because there are thousands of rows on mulitple pages, with individual cells color coded for different years and such, and lots of open rows in between. When I drag I then have to recolor cells and delete previously empty cells.

 

[Rick Rothstein]

In that case, instead of copying down (or across), what about making the change to the first cell, then COPY it; then select the column and click Edit/Find, put an asterisk in the "Find what" field, make sure "Look in" drop down shows "Formulas" (also make sure the "Match entire cell contents" check box is not checked), click "Find All" followed by CTRL+A, then close the Find dialog box; next click Edit/Paste Special and choose the Formulas option, then OK. That should copy the formula into filled cells only preserving the cell formats. The only reason I am pushing for a manual solution like this is because I don't know how to do what you want with just the Replace function.

 

[Roinda22]

I heart it!!! Thanks sooooo much. I won't be able to test it until tonight, but I am sure you have solved my problem.

Thanks again!!!

 

[Gerald Higgins]

Would this only search (CB$6) though? The problem is this cell reference changes, such as (CC$6).

In that case, maybe try something like

FIND
If(year(C?$6)=2004*

where the ? specifies a single wildcard character.

 

[Roinda22]

That works too! Thank you!!!