+ Reply to Thread
Results 1 to 8 of 8

I need a stock order sheet to be created from a stock inventory

  1. #1
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    I need a stock order sheet to be created from a stock inventory

    Hi Guys,

    i have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order.
    once the item hits a min low, the last cell (O) will display an order needs to be placed.

    I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?

    This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?

    sorry if there is a thread relating to this, im not actually sure how to even begin looking for a question similar.

    Thanks in advance
    Last edited by tamhav; 01-08-2014 at 02:08 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: I need a stock order sheet to be created from a stock inventory

    Can you upload a simple example of what you have? It's easier to work with if we see how yours is laid out (Go Advanced> Manage Attachments)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: I need a stock order sheet to be created from a stock inventory

    I'd probably run with a variation of the following array formula:

    =IFERROR(INDEX(B$3:B$21,SMALL(IF($H$3:$H$21="no",IF(MATCH($B$3:$B$21,$B$3:$B$21,0)+ROW($B$2)=ROW($B$3:$B$21),ROW($B$3:$B$21)-ROW($B$2))),ROWS(J$3:J3))),"")

    with alterations of course, to accommodate your ranges. This would assume that each row contains a unique identifier.

    I keep that formula bookmarked and reference it often for single-step transfers of unique data with a criteria.

    Post #10 https://www.excelforum.com/showthread.php?t=960736
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: I need a stock order sheet to be created from a stock inventory

    Thanks guys,

    i have attached a sample of what im looking to do if it will help

    Thanks again
    Attached Files Attached Files

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: I need a stock order sheet to be created from a stock inventory

    Here's my version.

    I filled in the Order Status in column O like you said in your description, to check if the items hit minimum status.

    On the 2nd sheet in A3:

    =IFERROR(INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$O$2:$O$20="Place Order",IF(MATCH(Sheet1!$A$2:$A$20,Sheet1!$A$2:$A$20,0)+ROW($A$1)=ROW($A$2:$A$20),ROW($A$2:$A$20)-ROW($A$1))),ROWS(A3:A$3))),"")

    and in B3:

    =IFERROR(INDEX(Sheet1!I$2:I$20,SMALL(IF(Sheet1!$O$2:$O$20="Place Order",IF(MATCH(Sheet1!$A$2:$A$20,Sheet1!$A$2:$A$20,0)+ROW($A$1)=ROW($A$2:$A$20),ROW($A$2:$A$20)-ROW($A$1))),ROWS(I3:I$3))),"")

    Note these are both array formulas confirmed with Ctrl+Shift+Enter to exit the cell, instead of exiting with Enter as normal.

    Then copy both downwards.

    (You can delete column R, I was just building the formula on the same sheet so I could see the ranges.)

    Order Sheet.xlsx

  6. #6
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: I need a stock order sheet to be created from a stock inventory

    Hi

    Sorry this is still pulling up a blank and does not continue through all the cells.

    Any other possible solutions?

    thanks again

  7. #7
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: I need a stock order sheet to be created from a stock inventory

    Hi Daffodil,

    i managed to get this to work but now it will only carry to H21, the second i try and get it to carry through to say H100 it stops working again.

    If you know why, please help
    Thanks

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: I need a stock order sheet to be created from a stock inventory

    You'll just need to expand your ranges.

    My PC is locked up processing 294,351 x 9 array formulas so all I can offer for the next hour or two is this:

    =IFERROR(INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!$O$2:$O$100="Place Order",IF(MATCH(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100,0)+ROW($A$1)=ROW($A$2:$A$100),ROW($A$2:$A$100)-ROW($A$1))),ROWS(A3:A$3))),"")

+ 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. VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell
    By akash1229 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-27-2015, 04:37 PM
  2. Inventory and stock sheet running total calculation
    By vivek_vx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2013, 06:56 AM
  3. Stock values linked to physical stock on seperate sheet
    By Andycoulton in forum Excel General
    Replies: 6
    Last Post: 08-28-2013, 05:15 AM
  4. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  5. Replies: 0
    Last Post: 10-28-2012, 07:22 AM

Tags for this Thread

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