Global criteria update for formulas?

  • Thread starter Thread starter Annabelle
  • Start date Start date
A

Annabelle

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.
 
Mark said:
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.
Click to expand...
You could also place it in a named constant, see Insert | Create

Texas Handly
 
Back
Top