A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Logic Formula using colors in the formula?



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2010, 07:41 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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


Ads
  #2  
Old April 21st, 2010, 09:42 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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  
Old April 22nd, 2010, 01:01 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.