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?

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" wrote in message
...
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?

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.




"Bernie Deitrick" wrote:

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" wrote in message
...
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?



.

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?


"goodfish" wrote:

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.




"Bernie Deitrick" wrote:

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" wrote in message
...
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?



.

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" wrote in message
...
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?


"goodfish" wrote:

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.




"Bernie Deitrick" wrote:

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" wrote in message
...
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?



.

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(ISO DD($AD2));ISODD($V2);ISODD($AK2)))

Thanks again.

"Bernie Deitrick" wrote:

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" wrote in message
...
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?


"goodfish" wrote:

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.




"Bernie Deitrick" wrote:

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" wrote in message
...
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?



.



.