Conditional Formatting, Multiple Spreadsheets, Multiple Worksheets - Please Help!

[chandelirious]

Let's say I run a parcel collection service.

I've got three vans on the road. I've got seven locations that I need to collect from. Each van has got a scanner that collects data, such as date, unique location number, and parcel barcode number collected.

Unfortunately, due to new members of staff, and changes in vehicle runs, sometimes one or more location is not getting collected.

I want to have a spreadsheet (even better, a dynamic sheet, if these are possible, that floats on the desktop much like a sticky note) with conditional formatting that simply says whether a location has been collected yet or not.

Problems that I have:

Each van has their own spreadsheet. Each spreadsheet is broken down into monthly worksheets.

How I think it should work:

Formula should look for date and unique location number in each of the three van's worksheets. Then, it would simply colour the cell in the new spreadsheet as green. Until a location is collected, it remains blank, or red, whatever.

Example:

Date Location 1 Location 2 Location 3 Etc
07.06.16 Green Green Blank Blank
08.06.16 Blank Blank Blank Blank
09.06.16 Blank Blank Blank Blank

Is this possible? Or is it too complicated for Conditional Formatting? Would I need to use VBA, which I have zero experience/knowledge of?

Thanks in advance for your help.

If you need additional information (because I have not explained it clearly!) please ask.