I have a table in which the headers are dates, and the values in the data body are the planned shipped quantities.
so the cell should go red if there are values in the cell and the header date is after today.
I put the formula into the conditional formatting section but when I use format painter it keeps the references absolute.
Please Login or Register to view this content.
Try editing the conditional formatting formula to manually delete the $ sign.
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
May be
= .... AG8>0)
??
Quang PT
Please look at this.
Shareez - sorry for off-topic interjection:
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
all the cells still read off of A1 which is not what I am looking for.
No, it isn't. Don't try to use the format painter for CF rules - it's hit and miss.
The way that Shareez has set it up is correct, and he's applied it to the range in question. When you look at the rule with, say, E2 selected, the rule will still say A$1, BUT it will be referring to E1 (because the column A has not been anchored thus: $A). This does make setting CF a tad confusing until you get to grips with it. Look at the formatting in the file Shareez attached - it is doing what you asked for.
ok, i was able to modify Shareez's sheet to give me what I was looking for, thanks everyone for your help!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
OK, so it is not completed yet. I realize that the problem is not with my or the new C.F. But with it being in a table and that the date is in the header row of the same table. Any suggestions on how to overcome this?
I have attached the sample culprit.
I am not sure what kind of the table, I tried to convert date from text to number with no success.
But, I try:
* Table Tools/ Convert to Range
* Format row 5 as date
* Type: 1/11/2017 again in B5, then B5+1 in B6, drag accross
Now data in row 5 are in date format.
Bebo is right - although the cell formatting has been set to date, the headings are being read as text. You need to redo the headers as per post #11.
So, this is part of a larger table I cannot convert to a range, any ideas on formulas that would work inside the CF that could make this work?
Because B5 is text and under mm/d/yyyy format, then :
=AND(DATE(RIGHT(B$5,4),LEFT(B$5,2),--MID(B$5,4,IF(LEN(B$5)=9,1,2)))>TODAY(), B6>0)
I ended up using =datevalue(), I tried it and it seemed to be more concise
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks