Announcement

Collapse
No announcement yet.

Excel 2000 conditional formatting quandry

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel 2000 conditional formatting quandry

    I'm creating a simple Excel 2000 Spreadsheet (Yes, I said Excel 2000... this place is a little behind the times) to keep track of computer information and one of the bits of information I'm interested in is when the warranty for a computer expires.

    I have a column where I place the expiration date. I want the cell to fill with yellow when the warranty is within 30 days of expiring and turn red on the day of expiry (and of course remain red ever after). I set up conditional formatting for the cells according to the attached picture. However, now when there is no data in the cell it fills in with red as seen in the second attached picture. Ick! I've tried to play with a third condition that fills with white if the value =null and =0 but to no avail. Anyone have any tips?

    Currently I just fill in the string "[Insert Warranty Expiration Date Here]" into the cell and it formats the cell with the standard white background with black text... but it looks tacky and seeing red should only be in the case of something drastic... like the warranty expiring. I don't want folks to get in the habit of ignoring red cells because of overuse.
    Attached Files
    Last edited by Nonapeptide; 24th September 2008, 02:31.
    Wesley David
    LinkedIn | Careers 2.0
    -------------------------------
    Microsoft Certifications: MCSE 2003 | MCSA:Messaging 2003 | MCITP:EA, SA, EST | MCTS: a'plenty | MCDST
    Vendor Neutral Certifications: CWNA
    Blog: www.TheNubbyAdmin.com || Twitter: @Nonapeptide || GTalk, Reader and Google+: [email protected] || Skype: Wesley.Nonapeptide
    Goofy kitten avatar photo from Troy Snow: flickr.com/photos/troysnow/

  • #2
    Re: Excel 2000 conditional formatting quandry

    I may have just offered more living proof to the concept that the best way to solve a problem is to walk away from it for a while before doing more research. The solution came from the comments at the end of this article.

    Now my conditional formatting looks like the attached picture and seems to be doing just fine. Does anyone see another / better way do do this? Any pitfalls that I'm unaware of?

    EDIT: Notice in the picture that the third condition says "greater than or equal to". Yes, that was wrong. It should be "Less than or equal to."
    Attached Files
    Last edited by Nonapeptide; 24th September 2008, 02:53. Reason: Duh
    Wesley David
    LinkedIn | Careers 2.0
    -------------------------------
    Microsoft Certifications: MCSE 2003 | MCSA:Messaging 2003 | MCITP:EA, SA, EST | MCTS: a'plenty | MCDST
    Vendor Neutral Certifications: CWNA
    Blog: www.TheNubbyAdmin.com || Twitter: @Nonapeptide || GTalk, Reader and Google+: [email protected] || Skype: Wesley.Nonapeptide
    Goofy kitten avatar photo from Troy Snow: flickr.com/photos/troysnow/

    Comment


    • #3
      Re: Excel 2000 conditional formatting quandry

      Looks good, and if it works, why mess with it!
      Obviously Excel 2007 would do it much better
      Tom Jones
      MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
      PhD, MSc, FIAP, MIITT
      IT Trainer / Consultant
      Ossian Ltd
      Scotland

      ** Remember to give credit where credit is due and leave reputation points where appropriate **

      Comment

      Working...
      X