+ Reply to Thread
Results 1 to 12 of 12

Needed...formula for fixed total....

  1. 05-11-2012, 09:59 AM #1
    Bembs21
    Bembs21 is offline
    Registered User
    Join Date
    04-18-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Needed...formula for fixed total....

    THIS IS AN INVENTORY SITUATION. I AM LOOKING TO HAVE A FIXED RUNNING TOTAL IN COLUMN "E" (QUANTITY ON HAND). i WANT TO ABLE TO CLEAR THE ADDED INFORMATION IN COLUMN "F" THROUGH "S" (SALES AND PO'S IN), BUT THE LAST TOTAL CALCULATED IN COLUMN "E" REMAIN, EVEN IF I CLEAR THE CELLS AND ADD NEW WHOLE NUMBERS. I WANT TO KEEP ADDING OR SUBTRACTING TO THE NUMBER ALREADY THERE IN COLUMN "E". THE FORMULA i HAVE NOW IS SIMPLE "SUM". IN COLUMN E TO SUBTRACT FROM THE COLUMN F-O ACROSS AND ADD FROM COLUMN S. WHICH WOULD WORK GREAT IF THE TOTAL IN COLUMN E STAYED WITH THE LAST NUMBER CALCULATED... I WANTED IT TO WORK LIKE THE YTD TOTALS...IF THIS MAKES ANY SENSE ...TO MAINTAIN THE VALUE OF QUANTITY ON HAND. ATTACHED IS MY FILE THAT I CREATED..GREATLY APPRECIATE ANY HELP....MAKE WHATEVER CHANGES NEED BE AND UPLOAD AGAIN...
    Attached Files Attached Files
    Last edited by Bembs21; 05-11-2012 at 01:06 PM.
    Reply With QuoteRegister To Reply
  2. 05-11-2012, 10:59 AM #2
    StevenM
    StevenM is offline
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Needed...formula for fixed total....

    Backup your data and run: YTDTotals
    I assumed you are working with whole numbers (that can be changed if not).

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 05-11-2012, 11:23 AM #3
    Bembs21
    Bembs21 is offline
    Registered User
    Join Date
    04-18-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Needed...formula for fixed total....

    Unfortunately this has not worked....the totals in column E go back to 0 when columns F-O,S are cleared...
    Last edited by Bembs21; 05-11-2012 at 11:29 AM.
    Reply With QuoteRegister To Reply
  4. 05-11-2012, 11:32 AM #4
    StevenM
    StevenM is offline
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Needed...formula for fixed total....

    I don't know what to say, the totals stay when I run this macro in the file you uploaded.
    The one mistake I do see is that:

    Please Login or Register  to view this content.
    Should have been:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 05-11-2012, 11:35 AM #5
    Bembs21
    Bembs21 is offline
    Registered User
    Join Date
    04-18-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Needed...formula for fixed total....

    Can you upload my file with this macro inserted...?
    Reply With QuoteRegister To Reply
  6. 05-11-2012, 11:54 AM #6
    StevenM
    StevenM is offline
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Needed...formula for fixed total....

    I don't know how to make the attachment look fancy, but ...

    Copy of Inventory Practice FILE.xlsm

    Then run the macro.
    Reply With QuoteRegister To Reply
  7. 05-11-2012, 12:44 PM #7
    Bembs21
    Bembs21 is offline
    Registered User
    Join Date
    04-18-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Needed...formula for fixed total....

    Unfortunately this has not worked....the totals in column E stay at 0 when columns F-O,S are inserted...no sums appear.
    Reply With QuoteRegister To Reply
  8. 05-11-2012, 01:19 PM #8
    StevenM
    StevenM is offline
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Needed...formula for fixed total....

    After you inserted sums in F-O, S, did you run the macro again?
    Reply With QuoteRegister To Reply
  9. 05-11-2012, 01:47 PM #9
    Bembs21
    Bembs21 is offline
    Registered User
    Join Date
    04-18-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Needed...formula for fixed total....

    Yes. And when I run the macro it changes the total. It does not add or subtract to the existing total.....

    If you look at my file column "E" is my quantity on hand...."F-O" are my daily sales for the week which subtract from my quantity on hand in column "E" and column "S" is Purchase Orders coming in which add to column "E"....What I am looking for is the value of column "E" to always stay with the actual quantity on hand and be able to clear values in the other columns.
    Reply With QuoteRegister To Reply
  10. 05-11-2012, 02:16 PM #10
    StevenM
    StevenM is offline
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Needed...formula for fixed total....

    And when I run the macro it changes the total. It does not add or subtract to the existing total.....
    Of course it doesn't, I deleted those. ... Oops ...

    Try this and see if this is better.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 05-14-2012, 07:27 AM #11
    Bembs21
    Bembs21 is offline
    Registered User
    Join Date
    04-18-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Needed...formula for fixed total....

    Negative....This did not work..and it took out the formulas i had for columns O-R....
    Reply With QuoteRegister To Reply
  12. 05-14-2012, 08:14 AM #12
    StevenM
    StevenM is offline
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Needed...formula for fixed total....

    Backup your data.
    Run UpDateYTDTotals_2 once.
    Add new data, then run: UpDateYTDTotals
    Is this the effect you want?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1