+ Reply to Thread
Results 1 to 6 of 6

I need a formula for an inventory workbook for to calculate sales and add inventory

  1. #1
    Registered User
    Join Date
    10-16-2020
    Location
    Calgary, Alberta
    MS-Off Ver
    10
    Posts
    8

    I need a formula for an inventory workbook for to calculate sales and add inventory

    Hi all I am new here. I am creating a spreadsheet for inventory for a charity who sells items to help others.
    My question is I have not used excel in years and I have made a workbook with 3 sheets, a Master, add Inventory, and Sales. now I have a dummy list of products so that I can just start with formulas. I have tried several formulas and i am just not getting it right.
    Firstly I need to add and delete add inventory and sales sheets from the master inventory sheet.
    Secondly I would like to auto populate the sales and add inventory sheets with the price when the product is entered
    I Would appreciate any help I can get .
    Thank you in advance I have also attached this basically empty spreadsheet ss
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: I need a formula for an inventory workbook for to calculate sales and add inventory

    Hi,
    Let's start with your 1st request -
    What are you trying to do with the formula you apply in G2?
    To sum the items in "Add Inventory" and subtract the "Sales" per item?

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: I need a formula for an inventory workbook for to calculate sales and add inventory

    Hi,

    Please notice the name of your products MUST be the same in all tabs.
    you had double space in "Bracelet 1" in Master sheet , whereas in the other tab with only one space; excel will not recognize them as the same expression and will not find a match, so you need to make sure they are typed the same. In order to avoid typos - you can create a Data validation like I did in the attached (in all 3 tabs - product name column), so that you just need to select your product from a given list.

    I amended the formula in G2, so that it brings inventory amount and subtracts the sales for each product:
    =SUMIF('Add Inventory'!C:C,'Master '!C2,'Add Inventory'!D:D)-SUMIF(Sales!B:B,'Master '!C2,Sales!C:C)

    For the price- D2 in "Sales":
    =IFNA(VLOOKUP(B2,'Master '!$C:$E,3,0),"")

    E2 in "Ad Inventory":
    =IFNA(VLOOKUP(C2,'Master '!$C:$E,3,0),"")

    Hope this is what you need and good luck with your project : )
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-16-2020
    Location
    Calgary, Alberta
    MS-Off Ver
    10
    Posts
    8

    Re: I need a formula for an inventory workbook for to calculate sales and add inventory

    Hi Belinda200,I appreciate your help so much. I changed my tab add inventory to stock in, and in input all the formulas yousuggested and they are all coming up with errors I will attach for you. I have looked these over and over to see if I had the columns wrong or cells, but i cannot see them. However i did change some of the cell references as i had to add a row. And yes i see where on bracelets I had two spaces, silly mistake. Can you take a look for me. When i am going through the formula to find error, i can see the cell reference in sales and stock in, are referencing correctly, but i dont see anything on master.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-16-2020
    Location
    Calgary, Alberta
    MS-Off Ver
    10
    Posts
    8

    Re: I need a formula for an inventory workbook for to calculate sales and add inventory

    Actually, I downloaded the one you sent back finally, i was having trouble since i was working from onedrive, (its so restrictive) and what you sent was exactly what i needed Thank you so much

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: I need a formula for an inventory workbook for to calculate sales and add inventory

    Great, you're welcome.

+ 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] Trying to Write a formula to calculate available inventory
    By OilAndGasMan1984 in forum Excel General
    Replies: 11
    Last Post: 09-19-2019, 02:43 PM
  2. [SOLVED] Formula for Inventory Summary based on Inventory List
    By EWolfe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2018, 10:35 AM
  3. Replies: 0
    Last Post: 09-04-2018, 02:52 AM
  4. [SOLVED] Excel Stock Inventory/Sales Workbook
    By johnmcluckie23 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-25-2017, 07:21 AM
  5. Replies: 0
    Last Post: 04-14-2014, 10:06 PM
  6. formula to calculate inventory, vlookup?
    By brianlg in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-23-2013, 08:35 AM
  7. Inventory Age Formula Needed for FIFO Inventory
    By SWeisser in forum Excel General
    Replies: 0
    Last Post: 07-20-2012, 02:44 PM

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