Conditional Formulas
 

I have an Excel spreadsheet that I run for our condo that lists the suite
numbers and the key numbers. The owners pay $12.00 for each key up to a
maximum of 4. Beyond that, they are charged $50.00. When an owner comes along
for another key, I would like the program to show quickly whether they owe
$12.00 or $50.00. They could come months later so there is no sequence as to
how many key they have except to labouriously check the list and count the
number of entries for their particular suite. I can do a sort by suite which
would show it up but I am wondering if there is a conditional formula that
can be used: i.e. IF(€¦THEN€¦) or something else.

Any help will be greatly appreciated.

Norm.

Conditional Formulas
 

Maybe use Conditional formatting on the column that you enter the Suite
number in......For cell A1, do Format ConditionalFormatting, then

Set Condition 1 as Formula is.....=AND(A10,COUNTIF($A:$A,A1)<5) and set
background color to green

Set Condition 2 as Formula is....=AND(A10,COUNTIF($A:$A,A1)<4) and set
background color to red........

Then click on A1 and then click on the FormatPainterBrush in the toolbar and
then paint it over the rest of column A as far down as you have data, or
expect to have data...........
Then, whenever you enter a Suite number in column A, if it has been entered
4 or less times, the cell will turn green, if 5 or more times, the cell will
turn red........

hth
Vaya con Dios,
Chuck, CABGx3



"Norm75" wrote:

I have an Excel spreadsheet that I run for our condo that lists the suite
numbers and the key numbers. The owners pay $12.00 for each key up to a
maximum of 4. Beyond that, they are charged $50.00. When an owner comes along
for another key, I would like the program to show quickly whether they owe
$12.00 or $50.00. They could come months later so there is no sequence as to
how many key they have except to labouriously check the list and count the
number of entries for their particular suite. I can do a sort by suite which
would show it up but I am wondering if there is a conditional formula that
can be used: i.e. IF(€¦THEN€¦) or something else.

Any help will be greatly appreciated.

Norm.

Conditional Formulas
 

Many thanks - I am sure this will do the trick. Norm.

"CLR" wrote:

Maybe use Conditional formatting on the column that you enter the Suite
number in......For cell A1, do Format ConditionalFormatting, then

Set Condition 1 as Formula is.....=AND(A10,COUNTIF($A:$A,A1)<5) and set
background color to green

Set Condition 2 as Formula is....=AND(A10,COUNTIF($A:$A,A1)<4) and set
background color to red........

Then click on A1 and then click on the FormatPainterBrush in the toolbar and
then paint it over the rest of column A as far down as you have data, or
expect to have data...........
Then, whenever you enter a Suite number in column A, if it has been entered
4 or less times, the cell will turn green, if 5 or more times, the cell will
turn red........

hth
Vaya con Dios,
Chuck, CABGx3



"Norm75" wrote:

I have an Excel spreadsheet that I run for our condo that lists the suite
numbers and the key numbers. The owners pay $12.00 for each key up to a
maximum of 4. Beyond that, they are charged $50.00. When an owner comes along
for another key, I would like the program to show quickly whether they owe
$12.00 or $50.00. They could come months later so there is no sequence as to
how many key they have except to labouriously check the list and count the
number of entries for their particular suite. I can do a sort by suite which
would show it up but I am wondering if there is a conditional formula that
can be used: i.e. IF(€¦THEN€¦) or something else.

Any help will be greatly appreciated.

Norm.