+ Reply to Thread
Results 1 to 10 of 10

Excel Stock Inventory/Sales Workbook

  1. #1
    Registered User
    Join Date
    01-19-2016
    Location
    Edinburgh
    MS-Off Ver
    Excel 2013
    Posts
    17

    Excel Stock Inventory/Sales Workbook

    Hi all,

    I have a workbook with 2 worksheets - Sales and Inventory (attached an example). The sales sheet documents all sales made by my company, with cost and quantity of the items sold, and the inventory includes how many of each we have in stock. I want this inventory to automatically update with the sales.

    Example:

    In the example workbook attached, we are selling tennis balls, footballs and rugby balls (ignore the cost) - we have 2 separate sales, each with different quantities for the different balls. On the inventory page, BEFORE taking those sales in to account, we have 10 in stock of each item.

    I want the inventory to automatically update to show that we only have 7 in stock on the inventory page for each item (given we sold 3 of each from 2 sales) - any help please? We do have 3 digit product codes for our products if it helps to add a column in with numbers instead.

    Best,
    John
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Excel Stock Inventory/Sales Workbook

    in Inventory sheet

    D5
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    E5
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Excel 2016 (Windows) 64 bit
    B
    C
    D
    E
    4
    Product In stock quantity Sale Qty In Hand Stock
    5
    Tennis balls
    10
    3
    7
    6
    Footballs
    10
    3
    7
    7
    Rugby balls
    10
    3
    7
    Sheet: Inventory
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    01-19-2016
    Location
    Edinburgh
    MS-Off Ver
    Excel 2013
    Posts
    17

    Re: Excel Stock Inventory/Sales Workbook

    Okay, that works plugging it in to the function, works great. However, I need to understand how this works (as my real inventory isn't as simple as this)...I've searched up the various parts of the function, and while I understand some of the reasons why you use the different parts, still pretty confusing

    What does the LEN and the MID mean and why do we use it?

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Excel Stock Inventory/Sales Workbook

    Len and mid is used to create criteria without "s". Teninis balls this is reason behind it. in your sale sheet products are with s .

    Hope this should help ..

  5. #5
    Registered User
    Join Date
    01-19-2016
    Location
    Edinburgh
    MS-Off Ver
    Excel 2013
    Posts
    17

    Re: Excel Stock Inventory/Sales Workbook

    I understand this, unfortunately not being able to transform this function in to my actual sales spreadsheet with any function.

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Excel Stock Inventory/Sales Workbook

    You upload your exact file after remove confidential information so that we can understand which kind of formula you will require.

  7. #7
    Registered User
    Join Date
    01-19-2016
    Location
    Edinburgh
    MS-Off Ver
    Excel 2013
    Posts
    17

    Re: Excel Stock Inventory/Sales Workbook

    This is the exact document with confidential info taken out.

    Instead of product name I am trying to use 3 digit product SKU to control inventory
    Attached Files Attached Files

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Excel Stock Inventory/Sales Workbook

    You selected format for QTY is text change it into General. Follow the steps to do so, select the column I in sheet sales the press control+****+~. once all the qty convert in to number formula would be work.

    F7
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag down
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    3
    Inventory
    Accurate as of: 25/09/2017
    4
    5
    6
    Product
    Product SKU
    Quantity On Hand
    Cost
    Sold
    Pre-selling quantity
    7
    x
    012
    11
    1
    12.00
    1
    8
    y
    018
    0
    9
    z
    005
    0
    10
    a
    014
    0
    11
    b
    036
    0
    12
    c
    003
    1
    13
    d
    021
    0
    14
    e
    022
    0
    15
    f
    031
    1
    16
    g
    033
    8
    17

  9. #9
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Excel Stock Inventory/Sales Workbook

    The numbers in column I (sales tab) are in text format. You need to convert them into numbers first before applying the SUMIF/S.

    Try the following in F7 (Inventory Tab):

    =SUMIFS(Sales!P:P,Sales!H:H,C7)

    I've converted the numbers stored as text in column I into actual numbers in column P.

    See the attached file.
    Attached Files Attached Files
    Last edited by cbatrody; 09-25-2017 at 07:31 AM.

  10. #10
    Registered User
    Join Date
    01-19-2016
    Location
    Edinburgh
    MS-Off Ver
    Excel 2013
    Posts
    17

    Re: Excel Stock Inventory/Sales Workbook

    Thanks cbatrody - works great now! Didn't realise it was something so simple, had tried many variations but glad to get there! THanks again!

+ 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. [SOLVED] Stock Inventory from Daily Sales, when a single product has various descriptions
    By barnesy1977 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-28-2017, 12:39 PM
  2. Replies: 0
    Last Post: 04-14-2014, 10:06 PM
  3. I need a stock order sheet to be created from a stock inventory
    By tamhav in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-20-2014, 10:40 AM
  4. How to use Excel to create the Inventory Stock
    By fong in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-05-2013, 11:49 PM
  5. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  6. Stock Keeping or Inventory Managing in Excel.
    By atthershabbir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2012, 12:35 AM
  7. Stock Inventory from Daily Sales, when a single product has various descriptions
    By barnesy1977 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-30-2012, 10:37 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