+ Reply to Thread
Results 1 to 4 of 4

Running balance column formula not auto adjusting properly when rows are inserted.

  1. #1
    Registered User
    Join Date
    10-30-2018
    Location
    Bradford, England
    MS-Off Ver
    2013
    Posts
    3

    Running balance column formula not auto adjusting properly when rows are inserted.

    Hello,
    When I insert rows into my table, the running balance formula doesn't properly adjust. Any help would be greatly appreciated.

    I have a table for financial projections into which I input any credits or debits I'm expecting so I often need to add and delete rows.

    =IF(AND(ISBLANK(C5),ISBLANK(D5)),"",SUM(E4,C5,-D5))

    This formula just sums the running balance from the row above (E column) and the credit and debit from the same row (C and D column). The IF part of the formula just keeps the running balance column blank below the last credit or debit entry (when the credit and debit entries are both blank). This is not necessary so if I need to lose this function that's fine.

    In the example above if I insert a row above row 5, the inserted row's formula adjusts correctly but the row below becomes

    =IF(AND(ISBLANK(C6),ISBLANK(D6)),"",SUM(E4,C6,-D6))

    The E4 cel reference should be E5 (ie the row above). All the rows below row 6 adjust correctly. Only the row below the one inserted ever contains this error. Of course, I could just keep copying and pasting the formula back in every time but this gets tedious.

    I'm sure plenty of you know what is going on. If you have time to enlighten me I'd be grateful.

    Following lack of answers here I've also posted this Q in msofficeforums and Microsoft Answers. I can't link to them as I haven't posted enough in this forum to be able to include links.
    Last edited by JPollard; 11-08-2018 at 08:40 AM. Reason: Correcting my explanation. Adding clarity in the title. Linking to my same posts elsewhere.

  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/365
    Posts
    52,004

    Re: Running balance column not auto adjusting properly when rows are inserted.

    Hi, welcome to the forum

    The simplest way to resolve this - dont insert new rows, just add the data to the end.

    If you MUST insert new rows, the quickest way around this, is to just copy the formula from above (the correct 1) and paste all the way 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
    10-30-2018
    Location
    Bradford, England
    MS-Off Ver
    2013
    Posts
    3

    Re: Running balance column not auto adjusting properly when rows are inserted.

    Thanks for taking the time to reply and for welcoming me to the forum. Inserting rows rather than adding data to the end is necessary so that future payments can be inserted in date order. Currently I do just copy and paste the correct formula all the way down, as you suggest, but that becomes a little annoying.

  4. #4
    Registered User
    Join Date
    10-30-2018
    Location
    Bradford, England
    MS-Off Ver
    2013
    Posts
    3

    Re: Running balance column formula not auto adjusting properly when rows are inserted.

    A user called Marcia at msofficeforums.com provided a nice simple workaround to this. In the running balance column first row enter..

    =IF(AND(ISBLANK(C4),ISBLANK(D4)),"",SUM($C4:$C$4)-SUM($D4:$D$4))

    then paste it down the whole column. This bypasses having to include the above running balance cel in the formula so allows rows to be inserted without the formula in the row below becoming messed up.

    This totals the credits and debits (C&D) columns up to and including the current row as a running balance in the E column. I'm not savvyenough to know how it is achieving this but I'm just happy it works!

+ 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] Adjusting Formula For Newly Inserted Column
    By general_excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-13-2016, 03:14 PM
  2. Replies: 8
    Last Post: 12-09-2013, 09:05 PM
  3. [SOLVED] AUTO INSERT A COLUMN'S FORMULA IN MANUALLY INSERTED ROWS
    By JLah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-20-2006, 11:15 AM
  4. [SOLVED] Rows inserted into a List will not sort properly
    By Derek Jones in forum Excel General
    Replies: 2
    Last Post: 11-08-2005, 11:10 AM
  5. [SOLVED] How do i keep a running balance without adding rows each time?
    By Ian in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 05:05 PM
  6. [SOLVED] How do i keep a running balance without adding rows each time?
    By park in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. [SOLVED] How do i keep a running balance without adding rows each time?
    By Ian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06037 seconds
  • Memory Usage 8,951KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1