Using Conditional Format to autoshade a calendar based on a series or date ranges

Hello there, first time posting and I'd really appreciate your help.

My problem:

My colleagues and I receive logsheets daily and we all use the same spreadsheet to input this data. The day and chronological order the sheets turn up at can vary so I have created a tab (i.e. OCT 2010) Where next to the 120 vehicles I have the following 3 coloumns:

FIRST BATCH ENTRY OF THE MONTH

First Date of Log Sheets / Last Date of Log Sheets / Miles Driven in this range

I then have this group of three coloumns repeated 10 times across because the logsheets can turn up in drips and drabs and not in any particular date order. i.e 23rd - 27th October, then next week 4th -5th October. All the mileage is then added up at the end.

Therefore I need some sort of alert for when we haven't received a certain range. At the moment I have a manually made calendar in the next tab that I manually highlight myself as the logsheets come in. Can I get conditional formatting to do this or is another method advisable?

Many Thanks and apologies for the bulk of text! (using Excel 2000)

Thankyou for your response, below is an image for the month of January 2011 as an example.

I have one tab for data entry and one tab which acts as a calendar to flag any missing entries we need to chase for the month.

http://img291.imageshack.us/img291/5270/examplea.png


The reason I need it like this is because:

A) The data has to be input as soon as it is receieved by various users

B) The data doesn't get sent in uniformly ie. it may be two weeks worth in one batch, it may two days worth and it also doesn't always get sent in in date order.

The crux of the matter is that as I enter those two dates (From and To date) I need the calendar to autoshade the range between the two. I am currently doing this manually with each entry but it seems unnecessarily time consuming as it is.

I hope this makes sense and your help is much aprreciated.

hello milesy 2000
As blastranger suggests, please attach a dummy sheet see we can see the formulas, etc.
The image attached doesn't really make entirely clear, we don't want to have to recreate your sheet so we can help.
Regards
Peter

Hello Peter

I am probably making what I require sound far more complicated than it is!

I'm not sure if this is what you mean by dummy sheet but I have attached my workbook with one month included as an example and irrelevant information removed. It isn't very formula intensive.

I just want the calendar sheet (Tab 2) to shade itself automatically as the data is entered on Tab 1 rather than how it's being done at the moment - clicking to Tab 2 and manually highlighting cells then adding a colour. I have a calendar like this premade for each month of data entry.

sorry milesy
were are you adding the info on tab 1 and which cell on tab two should be changing.
Lets look at MR A
Also the sheet seems to me as if you recreate it each month, I have a similar sheet for stuff, did you want the weekends to auto change colour as the month changes, the dates to auto change across the sheets

Let's say for example I get into my office and see that Mr A has sent some driver log sheets in that morning. On inspection I see that the first entry is for the 18th October and the final entry on the log sheet is for the 22nd October. So I input those dates into F7 and F8 respectively (as per the dummy sheet). I then take the end mileage from the start and put this data into F9 (total mileage). Finally I add up all the mileage that is miscellaneous (like a tyre change) and enter that into F10 (ineligible mileage).

The cells that should now be shaded to Green automatically are U7:Y7 on Sheet 2 (The date period on the calendar that I just entered). This allows me at a quick glance to see which driver hasn't sent in some of his logsheets.

And yes I do recreate it each month (create a copy and then adjust dates / shade weekends etc), I wouldn't know any other way. If this could all be an automated process that would even more beneficial.

Much Appreciation for your assistance Peter.

hello milesy
Please have a look at the attached, I think this is what you are after, please let me know.
I made the 2nd tab indicat which was the 1st, 2nd ect log sheet recieved.
If you had excel 2007 or later you could make each as a different colour. (2003 only has 3 cond formats available).
You put the start date in the first tab, it transfers to the 2nd sheet, the weekends are greyed out, .The days in the list which are not relevent, eg there is no 29/ 30 /31th feb in 2010, are highlighted.
If this is what you are after and need more info please let me know.
The second tab has got some formulas in the cells so you will have to be care full not to stuff them up. I would suggest protecting the secons sheet, i normally leave the password blank as it is normally a accedent that happens not an intentional stuff up.
If you need to copy the formula to more cells, copy D7, highlight D7 and all the rest of the cells you want the formula in, SPECIAL PASTE: FORMULAS.
Regards
Peter

Hello Peter

I have been away for the weekend as of Friday Morning so apologies for the delayed response.

This is fantastic and absolutely what I wanted. It works perfectly. Numbering the entries on the calendar was a great idea and I'm sure this will come in handy in future.

I do seem to be having a few minor issues.

I changed the dates to correspond with the real dates I need, so the file now starts in October 2010. When I make 2 new tabs for November 2010 (Data Sheet + Calendar) the orange shading for the FALSE day (November 31st) only appears at the bottom of the calendar. It begins to shade in cell AH133 rather than AH7. You will see what I mean in the attached file.

Also, I need to repeat this for 6 months (12 tabs altogether) up until March 2011. Is the quickest way to do this enter the date on the data sheet and then the date on the calendar sheet then go through the formula on the calendar sheet and change All 'OCT' references to 'NOV'?

Many Thanks Peter, you have been most helpful.

( I have included the link to the file in a PM as it was telling me the file was too big to attach.)