Hi all,
I have a spreadsheet that contains a great deal of information, though most if it contains dates. There are some colums that contain the word "NEED" if i don't yet have the date to put in there, and the cell is yellow. What i want to do is set up conditional formatting to change the cell to pink if:
A)the cell contains the "Need"
and
B)The date in the corresponding row from column C is more than 15 days old.
Is this even possible?
Thanks!
~Tink
Last edited by NBVC; 06-11-2009 at 09:20 AM.
Select all cells to be formatted and then
Try conditional formula:
=And(A1="Need",$X1<Today()-15)
Where A1 is the top-left most cell of your selection and X1 contains the date in that same row. (Note: the $ in front of the X to make sure all cells refer to that column always).
To invoke CF in XL2007... choose Conditional Formatting from the Home menu and then select New Rule.. then select Use formula to determine which cells to format.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hello Tink,
Welcome to the Forum!
Your post title should convey a brief description of what you need help with. Please do not use general words and phrases like "Help!", "need advice", "doesn't work", etc. Your present title doesn't tell anyone what your question is or what type of problems you are experiencing.
A better title would be "Condtional Formatting with 2 Criteria".
If you need helping in changing the title of your post let me know. Click hereto send me a message.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks for the reply, NBVC, and thank you Leith for explaining how is should describe the topic.
I tried using the formul =And(A1="Need",C1<Today()-15) but got an error. You said A1 is the upper left most cell in the area i'm selecting. I've actually selected all my columns because i'm trying to make it so it applies to the whole spreadsheet. In other words, whenever there is the word "Need", it checks back to that date in column C of that same row.
I'd say i'm making a mistake with my logic somewhere here. This is actually the last step to making a fairly complicated spreadsheet totally highlight itself based on lots of data!
If you are selecting all columns, then A1 should work.. also, am not sure about what cells contain what? Do all the cells potentially have the word "Need" in them or a date, or are they totally separate and only column C can contain dates?
If it is the former, you must include the $ sign before the C in the formula...
I don't know what error you are getting... I tried without error...
Maybe you can post a sample workbook if you can't figure it out?
I've attached a de-identified version of the workbook. As you can see, column C should either have a date, or simply be blank. The far left hand column has the name of a clinician and underneath are clients. So, if there is a break between clinician names, there might not be anything in that particular row. I'm sorry that this is probably a confusing spreadsheet if you aren't used to working with it
So are we only looking for "Need" in column F onwards?
Exactly! Any cell needing to change to pink would be from F onward.
Then select only those columns and invoke Conditional Formatting as I described above using formula:
=And(F1="Need",$C1<Today()-15)
and format as Pink.
Your workbook attached...
Wow, you did it. That's amazing! For some reason when i take that same code and put it in, it still doesn't work right. So, i just took the "sample" file you edited and pasted back in the names down the left hand side. You are clearly the man, I just didn't think that was possible. Thanks for your help!
Is there a place where you can see these things written out to figure out what you need for a formula, or is is just a lot of practice?
Thanks again!
Sorry, this post can be deleted, i had a dumb moment!
Last edited by Tink; 06-10-2009 at 08:34 PM. Reason: mistake
they are the same formulas you'd use in a work sheeet, practically anything that gives true or false.
e.gworksheet formula =if(a1=10000,"that's ten g's","that's not 10 g's")
is saying if a1 = 10000 then thats true so "that's ten g's" if it doesnt it's false so give the other answer.
but conditional formating only works on true/false so its sufficient to put
=a1=10000 if it does i.e true ,apply some formatting; if it doesnt then false so do nothing
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
while this is labeled as "Solved", i have another question about the spreadsheet.
Looking at row 680, when i put a date (6-9-09) into column H, the color goes to yellow instead of reverting to white. None of the conditions are set for it to be yellow. Does anyone have any idea about that?
Thanks!
It seems that in that particular cell you have a default background colour of yellow set...
In the Home tab.. select the paint bucket Icon drop down and select No Fill
Hmm. seems to be that way in all cells that are currently red. updating them puts them to yellow.
I also take it that they don't automatically uncolor themselves when they no longer meet the criteria for pink or yellow?
If no conditions apply, they should go back to the default colour (white)Originally Posted by Tink
Check the conditions ... through Manage Rules, select each and click Edit and you will see what conditions are set for the cell.
Note: If you don't mean to colour any of the cells without conditional formatting, then select all the columns you want to clean and apply the No Fill format... the colours that remain should only be due to conditional formatting.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks