Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-27-2010, 03:03 PM
woodland81 woodland81 is offline Advice on my Excel Project Windows Vista Advice on my Excel Project Office 2000
Novice
Advice on my Excel Project
 
Join Date: Dec 2010
Posts: 3
woodland81 is on a distinguished road
Default Advice on my Excel Project


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
Reply With Quote
  #2  
Old 12-28-2010, 11:38 AM
BjornS BjornS is offline Advice on my Excel Project Windows Vista Advice on my Excel Project Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 12-28-2010, 12:01 PM
BjornS BjornS is offline Advice on my Excel Project Windows Vista Advice on my Excel Project Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 12-28-2010, 12:33 PM
woodland81 woodland81 is offline Advice on my Excel Project Windows Vista Advice on my Excel Project Office 2000
Novice
Advice on my Excel Project
 
Join Date: Dec 2010
Posts: 3
woodland81 is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 12-28-2010, 01:09 PM
woodland81 woodland81 is offline Advice on my Excel Project Windows Vista Advice on my Excel Project Office 2000
Novice
Advice on my Excel Project
 
Join Date: Dec 2010
Posts: 3
woodland81 is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Advice on my Excel Project PST Advice Panzer Outlook 1 06-28-2010 06:29 PM
Connectivity issue of Project Professional with Project Server kashif Project 0 03-27-2009 06:37 AM
Advice on my Excel Project Gantt Chart: MS Project vs. Excel Nance Project 2 12-09-2008 01:59 PM
Error 9571 when opening Project Server from Project Pro mona Project 0 04-24-2007 07:50 AM
Seeking Excel Expert Advice for Web Publishing tjmichner Excel 0 10-25-2006 08:40 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft