+ Reply to Thread
Results 1 to 9 of 9

Simple stock inventory entry

  1. #1
    Registered User
    Join Date
    02-13-2019
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    7

    Simple stock inventory entry

    Hi guys,

    i want to make A SIMPLE STOCK SHEET, i just want a formula that can show blank cell, or maybe "0" when there is no entry for that day. I have attached a file for reference. Please help me T___T

    THANK YOU IN ADVANCE
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,349

    Re: Simple stock inventory entry

    In Q10 copied down:

    =IF(O10&P10="",0,IF(ISNUMBER(LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9)),LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9),0)-IF(P10="",0,P10)+IF(O10="",0,O10))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Simple stock inventory entry

    Or this slightly shorter one:
    =IF(COUNT(O10:P10)=0,0,SUM(O$10:O10)-SUM(P$10:P10))

    BSB

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Simple stock inventory entry

    In Q10
    Please Login or Register  to view this content.
    Then copy down.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Registered User
    Join Date
    02-13-2019
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    7

    Re: Simple stock inventory entry

    Quote Originally Posted by kvsrinivasamurthy View Post
    In Q10
    Please Login or Register  to view this content.
    Then copy down.
    i think this one works the way that i want precisely. thank you very much!!! <3

  6. #6
    Registered User
    Join Date
    02-13-2019
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    7

    Re: Simple stock inventory entry

    thank you for all your replies, thank you to all you amazing people!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,349

    Re: Simple stock inventory entry

    Quote Originally Posted by APEIND View Post
    i think this one works the way that i want precisely. thank you very much!!! <3
    This one does NOT produce what you want (or showed that you wanted) in row 17 - it produces -500 where you said you wanted 4700. Mine and BSB's both do exactly what you said you wanted.

    Excel 2016 (Windows) 32 bit
    N
    O
    P
    Q
    R
    S
    T
    9
    Date IN OUT BAL.
    AliGW
    BSB
    kvsrinivasamurthy
    10
    1
    5000
    300
    4700
    4700
    4700
    4700
    11
    2
    400
    4300
    4300
    4300
    4300
    12
    3
    200
    300
    4200
    4200
    4200
    4200
    13
    4
    0
    0
    14
    5
    500
    4700
    4700
    4700
    4700
    15
    6
    500
    5200
    5200
    5200
    5200
    16
    7
    0
    0
    0
    17
    8
    500
    4700
    4700
    4700
    -500
    18
    9
    500
    500
    4700
    4700
    4700
    4700
    19
    10
    0
    0
    20
    11
    0
    0
    21
    12
    0
    0
    22
    13
    0
    0
    23
    14
    0
    0
    24
    15
    0
    0
    25
    16
    0
    0
    26
    17
    0
    0
    27
    18
    0
    0
    Sheet: Sheet1

    If you want blanks instead of 0s, change mine to this:

    =IF(O10&P10="","",IF(ISNUMBER(LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9)),LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9),0)-IF(P10="",0,P10)+IF(O10="",0,O10))

    Excel 2016 (Windows) 32 bit
    N
    O
    P
    Q
    R
    9
    Date IN OUT BAL.
    AliGW
    10
    1
    5000
    300
    4700
    4700
    11
    2
    400
    4300
    4300
    12
    3
    200
    300
    4200
    4200
    13
    4
    14
    5
    500
    4700
    4700
    15
    6
    500
    5200
    5200
    16
    7
    0
    17
    8
    500
    4700
    4700
    18
    9
    500
    500
    4700
    4700
    19
    10
    20
    11
    21
    12
    22
    13
    23
    14
    24
    15
    25
    16
    26
    17
    27
    18
    Sheet: Sheet1


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by AliGW; 04-16-2019 at 05:09 AM.

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Simple stock inventory entry

    @AliGW
    Pl see The file I have attached. Function is working ok.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,349

    Re: Simple stock inventory entry

    Oh, I see - it's two formulae! Well, you don't need two - you can do it in one with my solution.

+ 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. Replies: 8
    Last Post: 02-03-2019, 04:24 PM
  2. Urgently Need A Excel Inventory Sheet With Stock In & Out Very Simple Functions
    By Shubhamjain96 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2018, 08:26 PM
  3. Replies: 1
    Last Post: 02-20-2015, 05:25 AM
  4. 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
  5. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  6. Replies: 0
    Last Post: 09-25-2012, 09:39 AM
  7. Inventory Stock Balancing with Inventory Data
    By bingbangboom in forum Excel General
    Replies: 2
    Last Post: 01-30-2011, 11:06 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