Global criteria update for formulas?

Is there a function similar to FIND / REPLACE for formulas? I have 56
places where I need to change the year in the formula from 2005 to
2006. Any help would be appreciated.


=SUMPRODUCT(--('Acme Invoices'!$F$6:$F$500="IET"),--('Acme
Invoices'!$J$6:$J$500="January 2005"),'Acme Invoices'!$K$6:$K$500)

Find/Replace works in formulas as well as constants. Select the cells
you need to change (Ctrl-click if the cells are not contiguous) and
then call up Find/Replace and tell it what to do.

To make life easier in the future, you could put the year in a separate
cell and have your formulas refer to them. If this cell was A1, you
would change

"January 2005"

in the formula you gave to

"January "&A1

(note the space after January). Then, when you change A1 (or whichever
cell you use), all your formulas update automatically.

I haven't tested this, but it should work. Try it on a single formula
to test it.

An excellent suggested solution. Thank you for your help.

Mark Lincoln wrote:
> Find/Replace works in formulas as well as constants. Select the cells
> you need to change (Ctrl-click if the cells are not contiguous) and
> then call up Find/Replace and tell it what to do.
>
> To make life easier in the future, you could put the year in a separate
> cell and have your formulas refer to them. If this cell was A1, you
> would change
>
> "January 2005"
>
> in the formula you gave to
>
> "January "&A1
>
> (note the space after January). Then, when you change A1 (or whichever
> cell you use), all your formulas update automatically.
>
> I haven't tested this, but it should work. Try it on a single formula
> to test it.
>
You could also place it in a named constant, see Insert | Create

Texas Handly