The formula below is in column U in 8 workbooks. Each of these
workbooks has twenty worksheet tabs containing the formula in 20 blocks
of rows within each worksheet. As you can see anytime the formula (An
addition or subtraction of an account # (See below)) changes it is a
task to cut & paste to update the formula.


=IF(AND(COUNT(O18:R18)<0)*(OR($C$4=202,$C$4=2273, $C$4=6401,$C$4=12400,$C$4=12423,$C$4=12703,$C$4=23 840,$C$4=32438,$C$4=34007,$C$4=50116,$C$4=50702,$C $4=50861,$C$4=50874,$C$4=12465,$C$4=61100,$C$4=622 55)),"_____","N/A")

Is there a way to change have a Macro change the formula based on a
sheet that will list the accounts to be used in the formula & then copy
& paste the updated formula into all worksheets in all the workbooks?

Any ideas? Thanks?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

A possible alternative:

First, the AND() isn't doing anything in the formula except
returning the same value that COUNT(O18:R18)<0 returns.

Put your account list in a separate workbook named, say, Accts.xls.
Name the range of accounts "acctlist" - the range can be the entire
column if desired. For the moment, leave it open.

In each of your workbooks, convert the formulas you have now to
something like:

=IF(OR(COUNT(O18:R18)=0, ISNA(MATCH($C$4,Accts.xls!acctlist,0)))
"N/A", "______")

That way you'd only need to update one workbook (Accts.xls) by
adding or deleting an account number, and not change any formulae.


In article ,
STEVEB wrote:

The formula below is in column U in 8 workbooks. Each of these
workbooks has twenty worksheet tabs containing the formula in 20 blocks
of rows within each worksheet. As you can see anytime the formula (An
addition or subtraction of an account # (See below)) changes it is a
task to cut & paste to update the formula.


=IF(AND(COUNT(O18:R18)<0)*(OR($C$4=202,$C$4=2273, $C$4=6401,$C$4=12400,$C$4=12
423,$C$4=12703,$C$4=23840,$C$4=32438,$C$4=34007,$C $4=50116,$C$4=50702,$C$4=508
61,$C$4=50874,$C$4=12465,$C$4=61100,$C$4=62255))," _____","N/A")

Is there a way to change have a Macro change the formula based on a
sheet that will list the accounts to be used in the formula & then copy
& paste the updated formula into all worksheets in all the workbooks?

Any ideas? Thanks?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/