If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Logic Formula using colors in the formula?
Hi:
I am using Office (Excel) 2007, and for some reason I thought that a change was made in this version of Excel, that allows for formulas which take into account the font color. Here is what I'm trying to accomplish: In Columns L & M I have dates that identify a beginning and end date for a class that a student has signed up for. If the student hasn't paid in full, the class dates are in bold red font. Once payment has been made, I change the font to black (and unbold it). The two issues I'd love to do with formula (to make things easier) are the following: Issue 1: If a date is identified in Column K (this column shows date student paid in full), the font in columns L & M should automatically change from bold red, to unbolded black. Issue 2: I'd like a formula that does a Countif to tell me how many clients, scheduled for a class have not yet paid (in other words, count all bold red font in Column K as this would identify the number of individuals who haven't paid for their classes yet). Issue 3: I'd like a logic formula (using an IF statement I suspect) that merely gives me a flag which states: "Pending Balances" if the font color in columnss L or M are red and bold. Thanks very much for any help in advance! Dan |
#2
|
|||
|
|||
Logic Formula using colors in the formula?
1) Assuming your base formatting in L & M is unbolded black, Conditionally
format those cells. (with row 2) Formula is: =ISNUMBER(K2) Format to bold, red font 2) Single formula is: =COUNTBLANK(K2:K100) or, if the cells in column K are not truly blank: =COUNTA(K2:K100)-COUNT(K2:K100) 3) Similar to above, formula would be: =ISBLANK(K2) or =NOT(ISNUMBER(K2)) -- Best Regards, Luke M "Danny Boy" wrote in message ... Hi: I am using Office (Excel) 2007, and for some reason I thought that a change was made in this version of Excel, that allows for formulas which take into account the font color. Here is what I'm trying to accomplish: In Columns L & M I have dates that identify a beginning and end date for a class that a student has signed up for. If the student hasn't paid in full, the class dates are in bold red font. Once payment has been made, I change the font to black (and unbold it). The two issues I'd love to do with formula (to make things easier) are the following: Issue 1: If a date is identified in Column K (this column shows date student paid in full), the font in columns L & M should automatically change from bold red, to unbolded black. Issue 2: I'd like a formula that does a Countif to tell me how many clients, scheduled for a class have not yet paid (in other words, count all bold red font in Column K as this would identify the number of individuals who haven't paid for their classes yet). Issue 3: I'd like a logic formula (using an IF statement I suspect) that merely gives me a flag which states: "Pending Balances" if the font color in columnss L or M are red and bold. Thanks very much for any help in advance! Dan |
#3
|
|||
|
|||
Logic Formula using colors in the formula?
Hi Luke!
The first outcome (Issue 1) worked using conditional formatitng. Thanks! However, the second and third did not, as I'm trying to count only those occurances that are in bold red with respect to Issues 2 and 3. Not sure if this can be accomplished through conditional formatting or VBA coding. Any help is welcome. Thanks in advance............Dan "Danny Boy" wrote: Hi: I am using Office (Excel) 2007, and for some reason I thought that a change was made in this version of Excel, that allows for formulas which take into account the font color. Here is what I'm trying to accomplish: In Columns L & M I have dates that identify a beginning and end date for a class that a student has signed up for. If the student hasn't paid in full, the class dates are in bold red font. Once payment has been made, I change the font to black (and unbold it). The two issues I'd love to do with formula (to make things easier) are the following: Issue 1: If a date is identified in Column K (this column shows date student paid in full), the font in columns L & M should automatically change from bold red, to unbolded black. Issue 2: I'd like a formula that does a Countif to tell me how many clients, scheduled for a class have not yet paid (in other words, count all bold red font in Column K as this would identify the number of individuals who haven't paid for their classes yet). Issue 3: I'd like a logic formula (using an IF statement I suspect) that merely gives me a flag which states: "Pending Balances" if the font color in columnss L or M are red and bold. Thanks very much for any help in advance! Dan |
Thread Tools | |
Display Modes | |
|
|