Hello again,



I intend producing a spreadsheet (Excel 2003) which holds training course details and the dates each employee must complete the refresher.

What I want to do is construct a formula which will show a "FLAG" which is 30 days before the date in the cell being looked at. For example. a training course is completed on 28 January 2014. The refresher for the course is annual and the next renewal would be 27 January 2015.

I want the flag to pop up, saying "REFRESHER DUE" in the same column/row (F7), 30 days before the due date of 27 January 2015. In order to further highlight the flag the cell showing the answer will be in red text.

What I have in mind is something like =IF(E7<=(E7-30),REFRESHER DUE,"") where Cell E7 has the date of the review.

For the life of me I cant get this to work.

I have attached a spreadsheet showing what I have in mind.

Grateful for any help.

Attached Files

Training Record DRAFT.xls (33.5 KB, 7 views)

Perhaps ?
Use CF to colour the text like =F7<>"" format red

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you for the prompt response.

It looks like your suggestion will work.

I'm not sure how to use the CF part of your response. Grateful of you could show where the =F7<>"" would be placed using the code you supplied?

Also, is it possible to shade the cell in yellow as well as red text?

Again, if so I will be grateful for a demonstration.

Thanks. I'm very pleased.

Supposing you need to use the formula in F7: F100,select that range. In the Home ribbon select CF, -New Rule -use a formula...-enter =$F7<>"" and set your formatting accordingly. ( red font and fill yellow) -Ok - Apply

I also noticed you are working with some weird merging of cells. Best to get rid of that

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks again for your help.

I did what you suggested and the outcome seems to be what I seek.

I have attached 2 spreadseets detailing how I will proceed. As you will see, A Brown is the source and Training Record DRAFT1 is where the details of all employees (there are 38) will be gathered together.

On the DRAFT sheet, is there any way to show the date of a Review which isnt due, ie rather than show a blank cell it will show the date the review is due and without any CF?

Also, as mentioned above, I have 38 individual records to produce and then transfer to the consolidated sheet. The easiest way to populate the consolidated sheet is using the COPY command. What I mean is enter the link on the consolidated sheet for example, in cell E3 and drag it to the right. However as the 2 sheets are constructed differently ie Portrait and Landscape, Copy and Drag isnt immediately possible. Is there anything I can do to make this possible? Or an aternative way to do it?

Very grateful for your continuing help and guidance.

P.S. Not quite sure what you mean about weird merging of cells. Unfortunately the format of the sheet was given to me and needs to be kept that way. However I am sure there is room to persuade the boss and improve things

Attached Files

	A Brown.xls (47.5 KB, 6 views)
	Training Record DRAFT1.xls (45.5 KB, 8 views)