excel problems

  broggs 19:42 23 Jun 2008
Locked

hello...a few excel problems for you all to ponder about.
First one...I want to put a date in a cell and when the date has changed to a predetermined date span from this date, the text flashes red in that cell.

e.g.ordered goods today so input 23/06/2008 in cell. after 2 days have passed we have not received confirmation of delivery date so this cell's text flashes red.

second problem is that different suppliers have different response times when acknowledging orders so need to be able to change the time of notification for each supplier.
thanks for your time

We only use a few suppliers on this sheet.
all the dates will be put in column F

  csqwared 21:18 23 Jun 2008

Think this might solve the problem. In your workshet set a cell (A1 for example) as a reference and format it as Date i.e. Format/Cell/Date, you may have to use the Custom option and set the format as dd/mm/yy. In this cell enter =NOW(). This should give you the current date and everytime you access the workbook it will update the date. In the 'Ordered Goods' date cell enter the date of order and then go to Format/Conditional Formatting where 'Cell Value is' - 'less than' - '=A$1$-2' and then click the 'Format' button and under the 'Patterns' tab select red. If you need more days before the cell changes alter the -2 to however many days you need.

Hope that's of use.

cc

  stlucia 08:46 24 Jun 2008

Essentially the same solution as csqwared's, but add an extra column for how many days lag you expect from that particular supplier, then use that cell reference in the forumula instead of the fixed -2.

  Ditch999 17:36 24 Jun 2008

I tried the above but could not get it to work in Excel 2007.
What does work for me is
1. Click on the date column letter to highlight it all
2. Select Conditional Formatting under Styles
3. Highlight Cells Rules
4. More Rules
5. Under "Edit the Rule Description" change Greater than to Less than
6. In the formula box type =TODAY()-2
7. Click on Format
8. Select the Font tab and change the colour to red
9. Select the Fill tab and click on No Colour
10. Click on OK and Apply to Exit

This should highlight any date more than 2 days old.

  VoG II 21:45 24 Jun 2008

Well you need to look at Conditional Formatting. However you should avoid flashing cells because

- this is not a native Excel capability

- most users will find this intensely irritating after a few flashes

- you may have users with disabilities such as epilepsy in which case flashing cells is a no-no.

  broggs 19:31 09 Sep 2008

thanks everyone....vog the flashing cell is to bug people so that they will investigate more rather than just overlooking it.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?