Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More conditional formatting (the saga continues) (Excel 2000 SP3)

    Thank you all for the help with conditional formatting in the past. It's been MOST appreciated. But of course, I have another quandary. I am working on an parts inventory spreadsheet, and I used conditional formatting to tell me when I need to reorder certain parts. The actual cell value is changed manually as parts go out, and then the conditional formatting kicks in when a part only has, say, 2 in inventory. HOWEVER...I have some parts that are listed twice, due to different pricing or coming in on a different invoice. So, what happens is where one entry for Hard Drives will say we only have 2 left and will turn red alerting me to reorder, upon careful inspection of the spreadsheet, I might find that we have 7 of a different type Hard Drive that is listed separately due to diff. price/invoice/brand and I actually DON'T need to reorder hard drives. Is there any way to perform conditional formatting on the sum of two cells...if you don't have a cell with that sum listed? OR...can anyone think of a better way to set up an inventory spreadsheet.

    Sorry so long and confusing.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More conditional formatting (the saga continues) (Excel 2000 SP3)

    You can use a formula instead of the cell value in conditional formatting. Ordinarily, you would specify something like Value is, Less than or equal to, 2.
    Instead, you can specify Formula is, =SUM(appropriate cells here)<=2.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More conditional formatting (the saga continues) (Excel 2000 SP3)

    Hans - you are a genius. Thank you for all your help. One question, though. I highlighted both cells, and applied the exact conditional formatting you suggested. However, only ONE of the cells takes on the formatting if the condition is true. Is that by design, or did I enter something incorrectly? I guess I thought that both would turn red when the condition is true.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More conditional formatting (the saga continues) (Excel 2000 SP3)

    Actually, I figured out the problem! The SUM range was changing for each cell. Went in and manually changed it and now all is well. Thanks again for your help!

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More conditional formatting (the saga continues) (Excel 2000 SP3)

    What you could have done was make the cell references in the formula absolute like this:

    <pre>=SUM($A$1, $A$20)<=2
    </pre>

    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: More conditional formatting (the saga continues) (Excel 2000 SP3)

    If the parts have unique identifiers - part name or part number, for example - that might be repeated in your listing but at a different price, you could try to base your conditional formatting on SUMIF. I've attached a small sample spreadsheet showing how this could work. There are two conditions set up; first if the units on hand are 1 or less (bright red background - urgent resupply) and the second if the inventory is 3 or less (bold red font - resupply required). In each case, the formatting uses SUMIF to total all identical part numbers within the list, so that even if an item is completely sold out but still available at another price and in sufficient quantities it does not have the formatting applied. If you have a 'minimum quantity for reorder' field available the conditional formatting could be used to check against that value instead of the hardcoded '1' or '3' used in my example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •