Im trying to use icon sets in column A (checkmark, Exclamation, and X)
B1:H1 contains expirations dates with conditional formatin: red if qual is lapsed, yellow if its due within 30 days and green if its over 30 days.
I need colum A to show the green check mark if all date are green, X if there are any red dates, and exclamation mark if there is any exlamation marks.
Anyway this can be done?
Well you'd have to have a check for A:A for each row individually if all conditions are met essentially.
Its probably easier to check for X first and then if none found, check for !, and if none found, return green.
Basically you can't count actual conditional formatting, you have to count whether the result is within a certain boundary.
have you tried to count based on what the CF is based on?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
how do i do that? all the cell is a date.
red: cell value less than =Today()
yellow: cell value less than or equal to =today()+30
Green: Cell Value greater than =today()+30
Maybe something like this.
1) =COUNTIF($A$1:$A$100,"<"&TODAY())
2)=COUNTIF($A$1:$A$100,"<="&TODAY()+30)
3)=COUNTIF($A$1:$A$100,">"&TODAY()+30)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
ok, so i think i figure out another way i can get this to work.
Say i wan to perform logical functions on the range E3:W3 which are all date values.
I want E2 cell value to be 3 if all dates on the E3:W3 range are >today()+30
I want E2 cell value to be 2 if any date on the E3:W3 range are >today but <=today()+30
I want E2 cell value to be 1 if any dete in the E3:W3 range is <=today
Then I'll use conditional formatting icon set with the following
checkmark if E2>2
exclamation if E2=<2>1
red X if E2=<1
any ideas?
=if(countif(e3:w3,">"&today()+30)=19,3,if(countif(e3:w3,"<="&today())>0,1,2))Originally Posted by jrafols
Isn't my suggestion, works for you?
This is actually brilliant...exactly what i was looking for....now...how do i add this to that formula?
i want e2 cell value to be 3 if all dates on the e3:w3 range are >today()+30 or the word "UP"
=if(sum(countif(e3:w3,">"&today()+30),countif(e3:w3,"up"))=19,3,if(countif(e3:w3,"<="&today())>0,1,2))
thanks for the assist Fotis, i couldnt get on the server at all yesterday, seems it it was down or something
what is wrong with this formula? it says it has too many arguments? I just wanted to add more criteria for showing number 1 and 3
=IF(SUM(COUNTIF(E3:W3,">"&TODAY()+30),COUNTIF(E3:W3,"up"),COUNTIF(E3:W3,"N/A"),COUNTIF(E3:W3,"Q"))=19,3,IF(COUNTIF(E3:W3,"<="&TODAY()),countif(e3:w3,"lapsed"))>0,1,2))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks