Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
hi.
now that i have one issue down, another pops up. i need help now with adding conditional formatting when i enter a formula to highlight column dates <= to a particular date. what i am doing is highlighting the entire column that i want to add the conditional format too, then i enter the formula =IF(E3<=DATE(2005,1,4),"yes","no") add my format and nothing happens. Please help. thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Try
=IF($E$3<=DATE(2005,1,4),"yes","no") You must also click Format, select the Pattern tab, and choose a colour. -- HTH RP (remove nothere from the email address if mailing direct) "jazzydwit" wrote in message oups.com... hi. now that i have one issue down, another pops up. i need help now with adding conditional formatting when i enter a formula to highlight column dates <= to a particular date. what i am doing is highlighting the entire column that i want to add the conditional format too, then i enter the formula =IF(E3<=DATE(2005,1,4),"yes","no") add my format and nothing happens. Please help. thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Not sure what you are doing with the formula. =IF(E3<=DATE(2005,1,4),"yes","no") But the way I read it - your "Conditional Statement" will return a "YES" or "NO" answer and is only applicable to cell E3. I read this as the date is in cell E3, the formula is in another cell, (i.e. F3). If you want to "Conditional Format" the "YES and "NO" then select that column and then select Cell Value in the "Conditional Statement" Box and enter "equals" then enter "YES" and then select your color or etc. Save that "Conditional Statement". The do the same again for "NO" If you want to "Conditional Statement" the Date (i.e. column E) then in the Cell Value select "less than or equal to" as the condition and then enter 1005,1,4 in the value section. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=496816 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
hi. sounds simple, but that's what i did--but nothing happened. for a
fresh start, i actually cut and pasted column into new workbook and got same results. I was looking for the rows based on my logic to be highlighted in yellow. Am i missing something? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
hi. sorry for the confusion. i have a list of dates in one column. I
highlighted the entire column and used the conditional formatting "formula is" =IF(E3<=DATE(2005,1,4),"yes","no") then selected the yellow pattern format. clicked "ok" twice and got nothing. I was hoping that in that column, some rows would appear highlighted in yellow based on logic stated above. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
you may need to copy the first formmatted cell to all the range -- gandhi318Posted from - http://www.officehelp.in |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
sorry, that does not work for conditional formatting, thanks.
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Clearly you are, but it is difficult to say what from a distance.
Can you provide an example of the data and each of the steps that you took so that we can spot the problem? -- HTH RP (remove nothere from the email address if mailing direct) "jazzydwit" wrote in message ups.com... hi. sounds simple, but that's what i did--but nothing happened. for a fresh start, i actually cut and pasted column into new workbook and got same results. I was looking for the rows based on my logic to be highlighted in yellow. Am i missing something? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Sure. I dumped hire date data from PeopleSoft Query to excel file. I
performed the steps to reformat the dates from yyyy/mm/dd to mm/dd/yyyy. highlighted the entire column, clicked on format - conditional formatting, selected for conditional 1, "formula is"; in the second field, i entered the formula =IF(E3:E5286<=DATE(2005,1,4),"yes","no"); clicked the format button, selected the Pattern tab, chosed the yellow color and clicked OK, twice. Still nothing. Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
You shouldn't use an IF function for this, the whole idea is to get a
formula that returns TRUE or FALSE then you shouldn't use the whole range (Bob's example doesn't use E3:E5286 does it?), you select the whole range with E3 as the active cell then in the formula is box put =E3<=DATE(2005,1,4) then select format and click OK twice if that doesn't work your dates must be text -- Regards, Peo Sjoblom (No private emails please) "jazzydwit" wrote in message ups.com... Sure. I dumped hire date data from PeopleSoft Query to excel file. I performed the steps to reformat the dates from yyyy/mm/dd to mm/dd/yyyy. highlighted the entire column, clicked on format - conditional formatting, selected for conditional 1, "formula is"; in the second field, i entered the formula =IF(E3:E5286<=DATE(2005,1,4),"yes","no"); clicked the format button, selected the Pattern tab, chosed the yellow color and clicked OK, twice. Still nothing. Thanks. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Yes it does, you can set conditional formatting for one cell and then copy
the format to other cells either by the paintbrush or by copy paste special as formats -- Regards, Peo Sjoblom (No private emails please) "jazzydwit" wrote in message oups.com... sorry, that does not work for conditional formatting, thanks. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
I tried the conditional formatting once again w/o using the IF, yes-no.
That did work, however, when I copied the format to the other cells, all of the cells were highlighted including the ones that were not part of my logic. for ex. I am looking for dates <=1/4/2005, and one of my dates, 1/18/2005 was highlighted. Should the formatting have skipped that particular date? Maybe I am not understanding the proper use of conditional formatting. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
hey, i got rid of the gremlin running around in my head, clouding my
thinking and performed the steps (correctly--this time) and it worked. thanks to all for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |