Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
I want to be able to search for two things in a column. The first is a value
of less than or greater than $100. (OK so that is really two things I guess). The second is to be able to see if that amount is within the previous 365 days. There is another column for the date. I can get part to work but am having trouble with the date. Any ideas would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Try {IF(A1<9,A1,"")}, in a separate column of use the conditional formatting
to change the color or highlight the field if it doesn't equal $100. "Russell" wrote: I want to be able to search for two things in a column. The first is a value of less than or greater than $100. (OK so that is really two things I guess). The second is to be able to see if that amount is within the previous 365 days. There is another column for the date. I can get part to work but am having trouble with the date. Any ideas would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Not sure what you mean by "within the previous 365 days".
if you're referring to 365 days prior to the current date =(B2<100)*(A2TODAY()-365) or if you mean 365 days prior to the value's adjacent date (with sorted dates) =(B2<100)*SUMPRODUCT(($A$2:$A1$A2-365)*($B$2:$B1=B2)) or (if dates are not sorted) =(B2<100)*SUMPRODUCT(($A$2:$A$1000$A2-365)*($B$2:$B$1000=B2)) HTH Steve D. "Russell" wrote in message ... I want to be able to search for two things in a column. The first is a value of less than or greater than $100. (OK so that is really two things I guess). The second is to be able to see if that amount is within the previous 365 days. There is another column for the date. I can get part to work but am having trouble with the date. Any ideas would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting
Thanks Steve. I am going to give it a shot. The more I read the more I think
that I am asking too much of Excel. At least the 2003 version that I am using. Have a good day. Russell "Steve Dunn" wrote: Not sure what you mean by "within the previous 365 days". if you're referring to 365 days prior to the current date =(B2<100)*(A2TODAY()-365) or if you mean 365 days prior to the value's adjacent date (with sorted dates) =(B2<100)*SUMPRODUCT(($A$2:$A1$A2-365)*($B$2:$B1=B2)) or (if dates are not sorted) =(B2<100)*SUMPRODUCT(($A$2:$A$1000$A2-365)*($B$2:$B$1000=B2)) HTH Steve D. "Russell" wrote in message ... I want to be able to search for two things in a column. The first is a value of less than or greater than $100. (OK so that is really two things I guess). The second is to be able to see if that amount is within the previous 365 days. There is another column for the date. I can get part to work but am having trouble with the date. Any ideas would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |