+ Reply to Thread
Results 1 to 5 of 5

auto update of values in rows when rows above is modified

  1. #1
    Registered User
    Join Date
    01-21-2014
    Location
    uae
    MS-Off Ver
    Excel 2007
    Posts
    14

    auto update of values in rows when rows above is modified

    Hi All,
    Please refer the attached sample work book, which contains daily sales figures - sales staff wise and channel of invoice wise. What I seek your support is to create a macro or some kind of formula which will auto sum the figures.

    Example;
    on day 01 Merwin sold 1 unit against DC10 (row 4) and with this sale his Month Till date (MTD) figures reaches 5 (row 6). On date 02nd, he sells 2 units but in DC80 channel (row 14) and the value in row 16 must change to 2 as soon as i press enter from key board. please note that the row 4 to 7 are constant and the position does not change every day. Only the daily values are updated to the daily sales made by the staff.
    My look out is that once the value in row 4 & 5 is inserted / updated, their cumulative values in row 6 & 7 must automatically sum up to the day before's value (or subtract if a minus value is inserted, due to sales returns)

    Can anyone help, please.

    last time one of the distinguished member helped in solving a similar need on "columns" through a vb macro. i am not good in VB and could not find the way to convert that for "rows"

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: auto update of values in rows when rows above is modified

    I Have created a User defined function for you

    Please Login or Register  to view this content.
    Will sum A1, A6, A11, A16, A21

    Please Login or Register  to view this content.
    Will sum A1, F1, K1, P1, U1

    Please Login or Register  to view this content.
    Will sum A1, A6, A11, A16, A21, F1, F6, F11, F16, F21, K1, K6, K11, K16, K21, P1, P6, P11, P16, P21, U1, U6, U11, U16, U21
    Attached Files Attached Files

  3. #3
    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,481

    Re: auto update of values in rows when rows above is modified

    If you keep the format the same for each subsequent table, you can use this, copied down (1 row) and across...
    =B13+B6
    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

  4. #4
    Registered User
    Join Date
    01-21-2014
    Location
    uae
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: auto update of values in rows when rows above is modified

    Thanks for the help. But this is not solving. The format is only from row 2 to 8. The bottom formats are given for giving you an idea how the format will change on day 2 and 4th. So u have to look only from row 2 to 8:
    The figures entered in row 4 gets added in sub ttl row 6 but reduces once it is modified or deleted. The sum value in row 6 and 7 should remain constant to its previous value even if the value on row 4 n 5 is deleted.
    Also no change happens in sub total row 7 which should get added up when a figure is entered in row 5.
    Can u pls hv a look

  5. #5
    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,481

    Re: auto update of values in rows when rows above is modified

    The sum value in row 6 and 7 should remain constant to its previous value even if the value on row 4 n 5 is deleted.
    This is not possible with regular formulas. If you have a formula that add's a value, when you remove that value, the formula will no longer include it. You will beed somebody to write some VBA code for this

+ 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: 12-09-2013, 09:05 PM
  2. Auto Update Rows in One Sheet from Another
    By tjhart in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 08-14-2012, 01:08 PM
  3. Auto hide unused rows, automatic update
    By Ranew in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-10-2011, 04:07 PM
  4. auto update formula if rows inserted
    By ktchegi in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 04-20-2007, 07:51 AM
  5. Auto update entire columns / rows???
    By Kcurtis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2005, 07:06 PM

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