Use of a Drop Down Box to Select Items and Change Dates and Conditional Formatting

All,

I am looking to creat a simple spreadsheet w/ 4 columns that will track an inspection schedule.

The four columns are FRR, Name, Date Inspected, Next Inspection Due By

Here is what I want to do.

I want to creat a drop down selection for FRR (I have 5 rankings). This determines the inspection frequency (< 12months, 12 months, 12-18 months, or 18-24 months)

I want the drop down selection to automatically calculate when the next inspection due by date in the last column.

I would also like the Name Column cells to change it's fill in color when the inspection due date is with in a certain time period: yellow 6 months out and red 3 months out.

Can someone help me with this. I just want a quick visual indicator.

(I have attached what I started). I know I need to create a seperate worksheet to create the drop down window. And I partially understand conditional formatting. I would also like the drop down to automatically appear in the FRR column as soon as a plant name is entered.

Thanks.

(Edited: I uploaded the file with some examples, at least to my abilities extent, as requested by the moderator. I included comments to assist with what I am looking for in this spreadsheet. I also deletd a worksheet that was not needed.)

Can you complete a few lines in the sheet showing some possible inputs and expected outcomes? And then repost the sheet.

First, the data validation (drop down menu)...

go to the FRR sheet, select A2:A5 and enter a name such as FRR in the Name Box just to the left of the Formula Bar at the top.

Then in the main sheet, select from A5 down to where you want to be able to keep making selections, say to A30, then go to Data|Validation and select List from the Allow menu... enter =FRR in the source field. Now you have dropdowns in those cells.

Next, Due by formula.

In F5 enter:

=IF(A5="","",EDATE(D5,VLOOKUP(A5,FRR!$A$2:$B$5,2,0)))

and copy down to same row as the data validation cells.

Next, Conditional formatting.

Question: Unless I misunderstood your formula above... then the difference between Last Inspection and Inspection Due is always fixed... and is based on column A input... so how/when will Last inspection be later than due date.

Note that you can't "freeze" results obtained by formulas..

Okay, for conditional formatting, select D5:D30 or whatever the bottom range is ... go to Home then Conditional Formatting and choose New Rule.

Select use a formula to determine which cells to format from top section and enter formula:

=AND($F5<>"",TODAY()>$F5)

choose Format and select Red from Fill tab.

Then click ok and click New Rule. Repeat above with formula:

=AND($F5<>"",$F5>TODAY(),$F5<=EDATE(TODAY(),3))

and choose Yellow,

repeat again with:

=AND($F5<>"",$F5>EDATE(TODAY(),3),$F5<=EDATE(TODAY(),6))

for Grey

You are welcome