Setting expiry date with conditional formatting

Hi Crilliams,

See the example at the bottom of his page to advanced filter date ranges:
http://www.contextures.com/xladvfilter01.html
Here is another date filter example at
http://www.get-digital-help.com/2009...in-excel-2007/

To get a name based on the date - Either the Advanced Filter or VLookup might work, depending on how you have your data set up.

Can you supply a short sample of your data? Press "Go Advanced" below the message area and then on the PaperClip Icon above the message area to upload/attach a sample file.

Thanks for the quick reply MarvinP,

The second link was helpful, and I understand what is happening.

I try the criteria in A1 - B1

="<=TODAY()+30" and =">=TODAY()" (is this correct?)

So that it is less then one month away, but greater then todays date, but it doesn't work for me.

Attached is a short sample. A4-A9 is the persons name and D4-D9 is the expiry date that I want to be notified of 1 month before

Thanks

Hi,
Find the attached which is an example of what you want. I've created 100 people with random dates as a Table. Then next to it is a Criteria with the correct syntax for the filter.

Now you need to look at the Advanced filter to see how it works. You need to tell the Advanced filter where the data is, where the criteria is and where you want to put the answer.

I hope this helps.

Hi,
The quick answer is no. It (that is the Advanced Filter) will update each time you update or run it. For this to happen automatically we'd need some VBA behind the worksheet that would fire a macro that updated it each time something happens.

Did you need that? Can you put your own vba in it? Use this one a while and learn how to do Advanced Filters and then in a few days, lets talk about "automatically"....

Hi Chris,

Advanced Filters took me a few days to understand. I'm still not an expert. It looks like, in your example you aren't setting up the data range correctly. Also you need to spell the headings "Exactly" the same in the Criteria range. Look at:
http://office.microsoft.com/en-us/ex...005200178.aspx and
http://www.bing.com/videos/watch/vid...VR5&FORM=LKVR3
to see if these help.

Hi Chris,

With the 3 second look at the Distr sheet you need a word in row 4 acorss all your columns. If you don't have that then Excel doensn't know it is a table. F4, K4 and M4 are blank they need a word in there. ALSO - who would have known this... Each of the other words in the 4th row must be different from each other. I see you have a few (1YR).

The Row 4 is the important one as it is at the head of your table. The 4th row should be the top row of your DATA and also the row you use as the head of your Criteria Range.

See what happens with that. We'll learn lots before we're done.