+ Reply to Thread
Results 1 to 17 of 17

formula to calculate inventory, vlookup?

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    formula to calculate inventory, vlookup?

    Maybe vlookup is what I am looking for?

    I have sheets with multiple columns, and 1000+ rows.

    Column B is a SKU number. Column F is quantity on hand.

    In a separate sheet (Sheet2) i want to look up each sku, and calculate the quantity on hand for every location that has that SKU.

    Would be even BETTER, if I could get excel to list data from an extra column (column A is location)

    example:

    Sheet2 should look like

    45568(sku)
    (location) | (quantity)
    (location) | (quantity)
    (location) | (quantity)
    _____________
    Total quantity on hand

    and I want to repeat this for every sku. sheet two would likely take up two columbs per sku, showing location in first, on hand in the second, then calculate a total

  2. #2
    Forum Contributor
    Join Date
    02-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    272

    Re: formula to calculate inventory, vlookup?

    There u go
    Attached Files Attached Files
    Please consider adding a * if I helped

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    I will look into this when I get home.

    Thank you for your kindness!

  4. #4
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    I am unsure what to look for in your sample sheet, can you elaborate

  5. #5
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    Still hoping someone can help with this.
    I am attaching an example of what I need. Sheet1 will have data, and sheet2 I want to calculate totals by SKU. Like I said, I want excel to show me a list of locations with the SKU I choose, then show the amount on hand per location, then tally a total.

    example.xlsx

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: formula to calculate inventory, vlookup?

    loc on hand
    loc on hand

    TOTAL ###
    what does that mean?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    thats the data that I want to have shown. open sheet1 for the data ill be using.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: formula to calculate inventory, vlookup?

    but it refers to nothing they are all the same

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: formula to calculate inventory, vlookup?

    See if the attached Pivot Table will give you what you want?
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: formula to calculate inventory, vlookup?

    See if this does as you need.
    Select from the drop-down in Sheet2 A1
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  11. #11
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    ahh! thank you both!

    Marcol, that is beautiful. Now I need to figure out how to apply that to my spreadsheets at work. I will investigate!

  12. #12
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    before I mark this thread as SOLVED, can someone help me be able to implement the work that Marcol did? is it a pivot table? where do I need to start?

  13. #13
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: formula to calculate inventory, vlookup?

    It isn't a pivot table.

    First you need a List of Unique SKUs, I used Remove Duplicates on a copy of your list in Sheet1 Column B
    From that you can get the matching descriptions, and use that for the drop-down Validation List.

    All this can be simplified by using dynamic named ranges to handle any variable amount of SKUs.

    1/. Does each location have the same amount of SKUs?
    2/. Can you have new and/or SKUs deleted?

  14. #14
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    Not each location has the same amount of SKUS
    I was able to work out the table on the right from your sheet2, but the dropdown list is giving me trouble

  15. #15
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: formula to calculate inventory, vlookup?

    Okay look back later tonight, I'll prepare a workbook for you, got to go for now.

  16. #16
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: formula to calculate inventory, vlookup?

    1/. First thing I would do is make your data continuous, don't have blank rows seperating your inventories for each location.
    This makes analysing your data much easier. (See Sheet "StockTake").

    2/. Add a new sheet for the list of unique SKUs and there descriptions, say Sheet "Lookups", this can be hidden

    3/. Use Names to define the various ranges dynamically.(See the Names Manager.)
    These will automatically adjust as your stocktake changes in size and/or values.

    Name:= "SkuList"
    Refers to:=
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Name:= "Unique_Skus"
    Refers to:=
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Name:= "Description"
    Refers to:=
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    4/. In Sheet "Lookups" A2 this array formula, confirm with Ctrl+Shift+Enter before dragging down.
    This returns all the SKUs found in your stocktake regardless of which location they fall in.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And in B2, to get the SKU description, this standard formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    5/. Use the name "Description" for your Data Validation list in Sheet "Report" A2
    And in B2 to get the selected items' SKU
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    6/. In A5 this CSE array formula, confirm with Ctrl+Shift+Enter before dragging down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Similarly in B5
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Referring to the SKU rather than the description should minimise the possibility of errors due to spelling mistakes.

    7/. Finally in B3, dynamically sum B5 down to last QOH found.

    This is bit of a ramble, see how you get on with it.
    Feel free to ask about any bit you don't understand.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    07-15-2013
    Location
    canton, ohio
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: formula to calculate inventory, vlookup?

    Thanks again Margol for taking the time. I guess my example should have been more fitting to what I am going to use this formula for.
    QOH is typically the F column. and each sheet that I work with has thousands of rows. Like I said, for the most part, each store will have the same SKUs, but some locations will have more or less depending on which are deactivated per location. I guess I just need guidance on how to format the dropdown and table that it produces.

    EDIT: didnt see that you had responded. Will read through your last post.

    Thank you!!

+ 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. Inventory Age Formula Needed for FIFO Inventory
    By SWeisser in forum Excel General
    Replies: 0
    Last Post: 07-20-2012, 02:44 PM
  2. How to calculate my inventory days when I have the consumption Data
    By Pravesh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-17-2012, 05:40 AM
  3. Excel 2007 : Calculate many months of inventory
    By noviceinexcel in forum Excel General
    Replies: 1
    Last Post: 10-22-2011, 01:30 PM
  4. Calculate Cost Basis of Inventory
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-15-2008, 07:49 PM
  5. how do i set up a worksheet to calculate the inventory?
    By mumbai in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2006, 08:20 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