Results 1 to 12 of 12
  • Thread Tools
  1. New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Good Afternoon,

    We have date wise requirement of our products. Suppose Finished goods available stock is 1000, the available stock will sufficient for how many days ( the cell should highlighted by some color automatically)


    Example sheet is attached.

    Pls give the solution to this.

    VVK
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Have a look at Conditional formatting (Format, Conditional format).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi pieterse


    In conditional formating we knew the color changing option.

    We need based on available stock the cells should be high lighted.

    Suppose the requiremet is 2000and available stock is 1000 it will come upto 10 days those cells only high lighted

    VVK

  5. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    CF allows you to do such things. See example.
    Change cell A2 to may first and cell B2 will turn red. Same goes for changing the # of days to a value of -say- 40 days.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi pieterse.

    Can you help on this.

    In Uploaded format. In that 3 products are there, the day wise requirement of each product is entered. In last cell as on date requirement total is summed, if the date changes the summed qty will change automatically, without manual intervention.

    Product 1 as on date requirement (20th may) is 614nos and available stock is 634nos. cells which are inline with 634nos should highlighted in green.

    Product 2 as on date requirement (20th may) is 327nos and available stock is 307nos. there is shortage of 20nos for 20th requirement. Cells which are inline with 307nos should highlight in green and cell which have shortage the cell should highlighted in red.

    Product 3 as on date requirement (20th may) is 101nos and available stock is 98nos. there is shortage of 3nos for 20th requirement. 20th actual requirement is 6Nos out of 6Nos we have 3Nos in stock. Cells which are inline with 95nos should highlight in green and 20th is having shortage of 3Nos it should highlight in yellow.


    If we did the report we can save around 1Hrs/day.


    VVK
    Attached Files Attached Files

  7. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts
    See the attached version.

    Look at Format | Conditional Formatting to see how it's done.
    Attached Files Attached Files

  8. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SHould have looked further down the message list.

    I built the exact same set of CF rules as you did Hans, then wanted to post, only to discover your file.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Excellent Job.

    Thank you very much.

    The day wise requirement of each product is entered. In last cell as on date requirement total is summed, if the date changes the summed qty will change automatically, without manual intervention.

    Example:

    Product-1 upto 20th total is 614nos and 21st qty is 20Nos. Summed total should change automatically if the system date changes

  10. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts
    You provide a statement, not a question. Is this a quote from a textbook or homework excercise?

  11. New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    Good Morning,

    Its a question.

    We are adding the requirements from month begining to as on date, to know the cummulative requirements

    Can you help on this, Daily we are adding the particular day cell. To avoid that can you give suggest best.VV

    vvk

  12. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts
    You can use SUMIF for that. In the attached version, I added the dates for the rest of the month, so that the dates are in B2:AF2.
    The formula for the total in row 3 up to and including today is

    =SUMIF($B$2:$AF$2,"<="&TODAY(),$B3:$AF3)
    Attached Files Attached Files

  13. New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Good Morning

    Thank you very much.

    VVK

Posting Permissions

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