Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting using Formulas and WEEKNUM | Excel Discussion (Misc queries) | |||
clear all except formulas, conditional formatting and validations | Excel Worksheet Functions | |||
Conditional formating using formulas | Excel Worksheet Functions | |||
Conditional Fromatting and Formulas | Excel Worksheet Functions | |||
how can i use colors in conditional formulas if i want the result. | Excel Discussion (Misc queries) |