+ Reply to Thread
Results 1 to 2 of 2

Compare demand against current stock and display how long current stock will last

  1. #1
    Forum Contributor
    Join Date
    04-06-2017
    Location
    Wolverhampton
    MS-Off Ver
    Office 365
    Posts
    437

    Compare demand against current stock and display how long current stock will last

    hello

    In A31 I want to enter current stock of boxes, I want it to look at whatís needed and work how long Iíve got stock for and turn green when there is stock avalable and red when not covered D27 TO K27

    it would be good if it could give a warning that we are running low somehow, like a reorder point, ideally we should keep 3 days in stock at all times



    Paul
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,499

    Re: Compare demand against current stock and display how long current stock will last

    This proposal employs a helper row which may be moved and/or hidden for aesthetic purposes.
    The helper row is populated using: =IF(COLUMN()=4,SUM(E27:G27),SUM(C28,G27))
    D27:K27 are initially formatted with green fill.
    The conditional formatting rule that applies red fill (out of stock) is: =SUM($D27:D27)>=$A31
    The conditional formatting rule that applies yellow fill (low stock) is: =AND(D28<$A31,E28>=$A31)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 04-26-2017, 12:06 PM
  2. [SOLVED] Formula for Purchase and sale total in current stock
    By vivek_vx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2016, 07:13 PM
  3. How to do Demand Planning & stock forecast ?
    By anu0512 in forum Excel General
    Replies: 6
    Last Post: 01-08-2016, 06:31 AM
  4. Replies: 31
    Last Post: 10-28-2015, 01:54 AM
  5. Identify the earliest expiry date on stock based on current stock levels
    By julie86xx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2013, 01:08 AM
  6. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  7. Replies: 0
    Last Post: 10-28-2012, 07:22 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1