+ Reply to Thread
Results 1 to 7 of 7

Creating a basic stock inventory sheet

  1. #1
    Registered User
    Join Date
    07-26-2014
    Location
    Dorset, England
    MS-Off Ver
    2003
    Posts
    4

    Question Creating a basic stock inventory sheet

    Hi All,

    I am very much a novice on excel so please bear with me!
    I am trying to create a very basic spreadsheet that I can use to keep track of stock items. What I want to do is to be able to update a particular stocks total when either stock is added or stock is taken. I am not very familiar with formulas, but what I want is when I add or remove stock the total updates accordingly, then if I need to update the same stock again, I can do so with the newly updated total now being the source that is updated - I hope that this makes sense?

    Hope someone can help.

    Best regards

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

    Re: Creating a basic stock inventory sheet

    Hi and welcome to the forum

    There are a number of different ways to do this, but seeing as you have not provided a sample to work from, see if this will get you heades in the right direction...

    B
    C
    D
    E
    1
    Code In Out Balance
    2
    aa
    20
    10
    3
    bb
    200
    100
    4
    cc
    2000
    1000
    5
    aa
    11
    9
    6
    bb
    111
    89
    7
    cc
    1111
    889
    8
    aa
    22
    31
    9
    bb
    2222
    2311
    10
    cc
    3333
    4222
    11
    aa
    44
    -13
    12
    bb
    444
    1867
    13
    cc
    4444
    -222

    E2=SUMIF($B$2:B2,B2,$C$2:C2)-SUMIF($B$2:B2,B2,$D$2:D2) copied down
    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

  3. #3
    Registered User
    Join Date
    07-26-2014
    Location
    Dorset, England
    MS-Off Ver
    2003
    Posts
    4

    Smile Re: Creating a basic stock inventory sheet

    Ford,

    Many thanks for your quick response - very much appreciated.
    If I do not want to have additional lines each time stock is updated can that be achieved?

    For example, if I only have 5 Stock Items;

    Stock A
    Stock B
    Stock C
    Stock D
    Stock E

    Each time Stock A is updated instead of creating a new entry can the first/original entry for Stock A only be updated?

    Best Regards

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

    Re: Creating a basic stock inventory sheet

    Not without VBA. A cell can contain either a value or a formula, not both. Besides, if you keep track of your stock the way I showed, you could maybe do analysis on which 1 sells more, which to focus on, etc

  5. #5
    Registered User
    Join Date
    07-22-2014
    Location
    Riyadh
    MS-Off Ver
    2010
    Posts
    88

    Re: Creating a basic stock inventory sheet

    update your new item details in master sheet, enter your receiving & delivery in data sheet then refresh your sheet to update pivottable.

    thanks
    Attached Files Attached Files
    Last edited by mohamedJamsheer; 07-26-2014 at 06:39 PM.

  6. #6
    Registered User
    Join Date
    07-26-2014
    Location
    Dorset, England
    MS-Off Ver
    2003
    Posts
    4

    Re: Creating a basic stock inventory sheet

    Ok, many thanks for your help.

    Best

  7. #7
    Registered User
    Join Date
    07-26-2014
    Location
    Dorset, England
    MS-Off Ver
    2003
    Posts
    4

    Smile Re: Creating a basic stock inventory sheet


    This is great- many thanks, very much appreciated.

    Best regards

+ 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. 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
  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. Creating an automatic stock list/inventory, HELP!
    By ibz1000 in forum Excel General
    Replies: 1
    Last Post: 08-01-2013, 05:35 AM
  4. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  5. basic stock take sheet update
    By nr6662 in forum Excel General
    Replies: 6
    Last Post: 01-27-2011, 04:30 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