+ Reply to Thread
Results 1 to 7 of 7

Trying to automate subtotals within a column of dynamic data

  1. #1
    Registered User
    Join Date
    05-30-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2016
    Posts
    4

    Trying to automate subtotals within a column of dynamic data

    I have a column in a spreadsheet, where various charges are added and need to be subtotalled. The problem is that each section varies in length so the positions where the subtotals need to be moves around.

  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
    80,780

    Re: Trying to automate subtotals within a column of dynamic data

    Welcome to the forum.

    Are you still using Excel 2003?

    There are instructions at the top of the page explaining how to attach your sample workbook.
    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
    Registered User
    Join Date
    05-30-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Trying to automate subtotals within a column of dynamic data

    Hi I am using Excel 2016. This is a reduced size version of the spreadsheet. Apart from the subtotals, it works OK, although I think there are probably better ways of constructing it.
    Essentially, there are 4 grades of staff, A to D, each with a different hourly rate. Pieces of work are timed in either hours and minutes, or units –( a unit being 6 mins, .1 of an hour).
    Data is input with column C being text description, column H being the Grade (A-D), and either the number of units or the hours & minutes. Column L just turns this into the total number of units.
    The relevant hourly rates for the Grades A-D are in cells M1, N1, O1 and P1, named RateA, RateB, RateC and RateD.
    Column D calculates the charge – it checks for input in column A. If there is something there (we use a * or a number), it takes the number of units of time from column L and multiplies by the relevant rate to get the amount to charge.
    When there is something is column A, columns E and F also populate – F copies the charge in D and E calculate the VAT.
    What I would like to do is to automate the subtotalling, in the example cells D8. D12, D17 and D20. The trouble is that each section is dynamic, and will move about as they have anything from 1 to 6 items to be totalled, and there will not just be four sections as in the sample, there may be 20 or 30. I can’t figure out any easy way to do this, so we do it manually as in the sample.
    We could input a code in column A say T2 to total previous 2 rows, T3 for previous three rows etc. I am pretty rusty on Excel, and don’t want to use macros if at all possible.
    Attached Files Attached Files

  4. #4
    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
    80,780

    Re: Trying to automate subtotals within a column of dynamic data

    Please change your user profile from Excel 2003 to Excel 2016. Thanks.

    This recent thread asked the same question: https://www.excelforum.com/excel-for...l-appears.html

    The second half of it in particular may help you.
    Last edited by AliGW; 10-02-2021 at 05:10 PM.

  5. #5
    Registered User
    Join Date
    05-30-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Trying to automate subtotals within a column of dynamic data

    Thank you for your reply, and I have changed my profile. The above doesn't work in my example, as there is already a formula in the cell where the subtotal would need to appear, and the 'empty' sheet prior to input, has no subtotals defined at all. They could be required to start at approx row 25 anywhere through to row 200. I need a way of putting something in a different column to 'trigger' the cell to subtotal the previous batch of input rather than the normal hours*rate cost calculation.

  6. #6
    Registered User
    Join Date
    05-30-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Trying to automate subtotals within a column of dynamic data

    I have just had a closer look at that thread you suggested, and it does help - I think I can sort it from here. Many thanks for your help.

  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
    80,780

    Re: Trying to automate subtotals within a column of dynamic data

    Just shout if you need any further help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ 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] Find subtotals for three separate items and place all subtotals at bottom of all the data
    By skylinekiller in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-09-2019, 09:22 AM
  2. Creating a macro to automate a Subtotals task
    By pharmerjoe7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2017, 12:42 PM
  3. Replies: 1
    Last Post: 11-22-2015, 08:35 AM
  4. Automate Dynamic Data push across Workbooks
    By Sunk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2015, 02:31 PM
  5. Need dynamic VBA code to find last row of data (one row before subtotals)
    By FELDY in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-16-2012, 02:44 PM
  6. [SOLVED] Automate Subtotals Insertion?
    By Thief_ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2005, 10: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