Closed Thread
Results 1 to 17 of 17

running total formula for inventory

  1. #1
    Registered User
    Join Date
    06-07-2007
    Posts
    8

    running total formula for inventory

    I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.

    Column F, Row 7 (This will be a new/different number entered every day)
    Column G, Row 7 (Begins with a starting total of 2545)

    I need Column G to add to it whatever number is entered into Column F, Row 7 and to keep the new total (ex. enter 10 into Column F/Row 7 & the total of Column G/Row 7 becomes 2555; if I then enter 20 into Column F/Row 7, Column G/Row 7 adds 20 to the original 2545, not the new total of 2555).

    Column G is not keeping the new total, it reverts back to the original number of 2545.

    Can anyone help with this? I don't even know if it's possible to work out this way.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    G7: =2545+F7


    HTH

    rylo

  3. #3
    Registered User
    Join Date
    06-07-2007
    Posts
    8
    Thanks, but that formula will not work (i've already tried this) as G7 always reverts back to the original number of 2545. When G7 is changed (ex. 2555 if I have entered 10 in F7) it does not keep the new number of 2555, it reverts back and adds to the original number of 2545.

    I need G7 to keep the new total (ex. 2555) and add the next entry into F7 to it and keep the new total once again.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Bit confused here. Your original post wanted G7 to retain the base number of 2545 and add what ever was posted to F7. So if you put a 10 in F7, then G7 would be 2555. If you then put 20 in F7 then G7 would be 2565. If you then put 0 in F7, G7 would come back to the original 2545.

    If you want G7 to continually increase from 2545 for each and every number put into F7, then you will need a code solution. Given the scenario above, G7 would progress from 2545 to 2555 to 2575. Is that what you want to happen?

    rylo

  5. #5
    Registered User
    Join Date
    06-07-2007
    Posts
    8
    Yes, that is exactly what i need it to do.

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Right click on the relevant sheet tab, select view code and insert

    Please Login or Register  to view this content.
    Put 2545 in G7, then put your required number in F7. G7 will continue to change for every change to the value in F7.


    rylo

  7. #7
    Registered User
    Join Date
    06-07-2007
    Posts
    8

    Smile

    THANK YOU, THANK YOU, THANK YOU!!!!

    That is exactly it! It works & gives me everything I need!!

  8. #8
    Registered User
    Join Date
    06-07-2007
    Posts
    8
    This is great & works for this line.

    What do I enter if I want this to apply to the entire spreadsheet?

    Thanks!

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    When you say the entire spreadsheet, do you really mean column F as the entry and column G as the progressive increment?

    If not, then can you please give relevant references.

    rylo

  10. #10
    Registered User
    Join Date
    06-07-2007
    Posts
    8
    Yes, that is what I am looking for.

    Thanks!

  11. #11
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Here goes

    Please Login or Register  to view this content.

    rylo

  12. #12
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hey Rylo,
    Thanks a lot , It will surely help me in the future, as I am preparing to make a similar Excel sheet....

    Reuben

  13. #13
    Registered User
    Join Date
    06-07-2007
    Posts
    8
    Thanks so much Rylo! You've been a big help.

    Now, am I able to make also an entry so that on the same spreadsheet that will subtract from column I what I enter into column H?

    The same concept as column F & G, only I want column I to be an ongoing total of what inventory is used.

    Thanks, Trace

  14. #14
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Trace

    Sure. Expand the current event to include another if statement.


    Please Login or Register  to view this content.

    rylo

  15. #15
    Registered User
    Join Date
    06-07-2007
    Posts
    8
    This is PERFECT!!

    Thanks again Rylo you've been a tremendous help!!

  16. #16
    Registered User
    Join Date
    08-06-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: running total formula for inventory

    Hello RYLO,
    I have a similar situation but I'm using 2 worksheets, S_V and Remaining.
    On the S_V Worksheet I have the LastName on A5 then from B5:H5 I'm using them to capture Sick Hours (b5 for Mon, c5 for tue,....,h5 for Sun) and then P5 =SUM(B5:H5) is the sum of the Sick Hours for that week
    On the Remaining Worksheet I have LastName on A5 for the same employee on S_V!A5 then on C5 is supposed to have the running total of Sick Hours (C5 = C5 + S_V!P5) for its matching employee on S_V!A5

    Following your respond to TRACE1287
    Column G, Row 7 is the same as my Remaining!C5 and
    Column F, Row 7 is the same as my S_V!B5:H5 or S_V!P5

    Please let me know how this can be done applyed to the spreedsheet or coulmns as the progressive increment? ( Any change on S_V!P5 updates Remaining!C5;Any change on S_V!P6 updates Remaining!C6, etc)


    Thanks!

    Martin

  17. #17
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: running total formula for inventory

    Martin

    Please review our forum rules, specifically rule 2, and start your own thread.

    Place a link reference to this post if it is relevant.

    rylo

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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