editing conditional formating formulas

[goodfish]

Hello!
I am wondering if it is possible to replace text of a CF formula with new
text given by the user through an input box .
I need it to do this throughout every sheets' CF formulas within a workbook
and every time a new customer is added to the spreadsheet.
I have already made the CF formulas for new customers by entering newa,
newb, newc......newp which need to be replaced with customer names. And I
have already set the range on which to apply the CF rules.
Any ideas?

 

[Bernie Deitrick]

It is unnecessarily complicated. Use a CF formula that doesn't need to be changed, one that
references a cell, range, or dynamic named range. Post what your criteria for the CF is currently,
and we can help you with that.

HTH,
Bernie
MS Excel MVP

 

[goodfish]

Hi Bernie!
I was thinking it might be complicated but I was trying to avoid the user to
have to mess around with conditional formulas!
Basically I have 3 tables on three different sheets and a list on the fourth
sheet.
These keep track of orders, contracts, shipments and invoices etc.
To make the data more easily leggible I have set up CF formulas to highlight
rows with a different colour for each customer, in fact I use 2 shades of the
same colour for each customer so to distinguish two succesive contracts from
any one customer.
I have also set up additional CF formulas so that when a new customer is
inserted all I have to do is replace the e.g. "newa" part of the formula with
a new customer name.
These are the formulas for one sheet (they refer to a helper column):
=AND(ISNUMBER(SEARCH("*newa*";$A2));ISEVEN($AG2))
=AND(ISNUMBER(SEARCH("*newa*";$A2));ISODD($AG2))
For the first sheet both formulas apply to:
=$A$2:$R$40
This range changes automatically as rows are inserted in the tables/list.
The last sheet has an additional formula to be modified which is:
=ISNUMBER(SEARCH("newa*";$D$35))
Does this help?
The only other way around I can think of is to use vba to make up the
formulas and cf rules but that sounds even more complicated.

 

[goodfish]

Hello again!
I was just reading my post again and an idea sprung to mind.
On the right of my tables/list there is a helper column for each
customer....in the example I provided the helper column is AG where cell AG1
is the "helper column header row" and it contains the customer name. for new
customers I have already set up the helper columns with the header as "newa",
"newb" etc.
So now if instead of "newa" I make the CF formula search an indefinite value
in AG1 then the conditional formating should still work.
What would be the best formula to use?

 

[Bernie Deitrick]

goodfish,

You have a lot of options - you could enter a keyword into a helper column:

=AND($AH2="New";ISEVEN($AG2))
=AND($AH2="New";ISODD($AG2))

You can get as creative as you want, as long as your formula returns True or False: Enter a date and
use that value to determine if the customer is new... something like

=AND($AH2="New";ISEVEN($AG2);$AI2>(TODAY()-7))

to only highlight new customers who have done something in the last 7 days...and on and on...

HTH,
Bernie
MS Excel MVP

 

[goodfish]

Hi Bernie and thanks for the help.
I have modified the helper columns a bit and avoided referencing the
customer name all together in the cf formulas.
Now, to highlight a row I just have a cf formula to check whether one of
three helper columns is odd (apply one colour shade) and one cf formula to
check whether one of them is even (apply other colour shade). If any of the
helper columns is neither odd or even the helper columns will still contain a
formula so I also need the cf formula to pick up errors and if so ignore them
and check the other helper columns.
Don't know if it can be simplified further but I have come up with the
following formula
=IF(ISERROR(ISODD($V2));ISODD($AD2);IF(ISERROR(ISODD($AD2));ISODD($V2);ISODD($AK2)))

Thanks again.

 

[Bernie Deitrick]

goodfish,

I think your formula may not give you the result you think it will. Try
moving your three columns together, say, starting in AK, and use the array
formula (enter using Ctrl-Shift-Enter)

=SUMPRODUCT(NOT(ISERROR(AK2:AM2))*(MOD(AK2:AM2,2)=1))>0
=SUMPRODUCT(NOT(ISERROR(AK2:AM2))*(MOD(AK2:AM2,2)=0))>0


in two cells - say, AN2 and AM2, and simply use

=AN2
and
=AM2

as your CF formulas.

HTH,
Bernie
MS Excel MVP

 

[goodfish]

Thanks Bernie!
Here is an update, (the final solution!)
It would have been a bit impractical to move columns together and what is
more the three helper columns (for each colour) became four (for each colour)
to allow for additional customers and may one day become 5 etc. so after
numerous attempts I managed to simplify the cf formulas to test for 1 odd
column down to
=ISODD(SUM($W2;$AD2;$AK2;$AR2))
and to test for 1 even column
=AND(ISEVEN(SUM($W2;$AD2;$AK2;$AR2));SUM($W2;$AD2;$AK2;$AR2)>0)
For some reason this way does not require ISERROR parameters (which is great)

So I am quite pleased that it all came down to something very simple even
though there were complications on the way!
Thanks again for your help.