If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
conditional formatting
Try {IF(A19,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
|
|||
|
|||
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 =(B2100)*(A2TODAY()-365) or if you mean 365 days prior to the value's adjacent date (with sorted dates) =(B2100)*SUMPRODUCT(($A$2:$A1$A2-365)*($B$2:$B1=B2)) or (if dates are not sorted) =(B2100)*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
|
|||
|
|||
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 =(B2100)*(A2TODAY()-365) or if you mean 365 days prior to the value's adjacent date (with sorted dates) =(B2100)*SUMPRODUCT(($A$2:$A1$A2-365)*($B$2:$B1=B2)) or (if dates are not sorted) =(B2100)*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. |
Thread Tools | |
Display Modes | |
|
|