Hello,



I have a project at work to do using Excel. I know the basics and what I was told was a requirement didn't think it was possible so started googling excel functions and capabilities and maybe design it around that.

I thought maybe on the Forums, if I create my basic design, those who advanced knowledge could comment or even suggested what would be good for me to add or remove. All opinions would be great.

The basic outline is we have a list of vehicles & details on the vehicles. These are going to have advertising put on them and will need to record the Start and End Date of current advertising. Some sort of alert is necessary to bring it to attention of when one advertising is coming to an end.

Ive successfully done Todays Date minus due date and a column showing how many days until Due. and added some basic Conditional Formatting.

I inherited the spreadsheet and added few bits myself. The calendar bit was already there, assume its to Log who's advertising is covering what dates. Is there anyway for those square to be auto completed when you enter Start and End Date?

From my research I've seen you can add Buttons to create an alert drafted email that extracts info from the sheet, in my case maybe that would be the Vehicles Due within 7 Days.
Each time an advert ends and new one starts I'd have to manually alter the start and end date. maybe i need some way of recording the info on each advert. Maybe an Archive Button that Sends the info to another Sheet where I just have to enter whos advert it was and then could filter out the truck and view all the past adverts.

I guess im speculating theories which I have no idea how to implement : - /

Any words of wisdom or advice would be brilliant. or if you think what ive got so far isnt very professional.

http://www.mediafire.com/?ew1qsw09dpa60fe

Hi,
here is one suggestion.

Enter this formula in cell K4:
=IF(AND(K$3>=$G4,K$3<=$H4),"x","")

Then copy and "paste special" (only formulas) this formula to ALL other "date cells".

Now there is an "x" in all cells within the advertising interval. You can now easily use conditional formatting to make the cells with an "x" have another colour. Of course you can also from the beginning enter this formula as a conditional formatting (using "where the formula is..." instead of "cell content").

Kind regards
Bjorn

Hi again,
another suggestion. I would add a column between "H" and "I", which indicates if you hace actually "stopped" the advertising (enter "Yes" manually here if you have "stopped" it).

Then I would use this column the enhance the conditional formatting for the date check (red, green, yellow), to avoid a "red" to appear if you already have "stopped" the advertising (= "the new column has a YES in it").

I hope this explanation was clear, just tell me otherwise!

Kind regards
Bjorn

First suggestion that looks excellent, i was playing around and using a more updated spreadsheet than id uploaded. wondered why it wouldnt do anything for me. certainly gonna cut down amount of data input, which obviously Id be duplicating. im well impressed. gonna look at your second bit now

hmm i've just been thinking.

ok advertising on a vehicle is going to start and finish. and then another one start start to maximise revenue.

If I change the Start and End/Due Date for the next advertising. It deletes the X's from the Calendar bit and complete teh new bit.
It would be beneficial to keep that so can keep track of who advertised and when.

Unless when one is ending I could somehow send to Archive the info on that truck to another sheet and simply go ahead and change the Start/Due Dates for the next advert

Archive Reg, Start, Due Date, Duration and I enter who advertised manually