Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Generate Subtotals (Excel 2000/XP)

    Okay, attached is a variation of the same sheet I've been working on for a couple months, now. You've helped me group sheets so updates could be done on the entire workbook and someone figured out a way to automatically group and ungroup the rows based on the entries in the first four columns. Someone helped me add project numbers to the sister sheet of the one I've attached. And yet someone else helped me get the lines and shading to appear automatically.

    All of that is disabled in the attached. What I'm looking for now is a formula that I can put in the cells that are currently displayed in red that will automatically pick up the subtotal range, so that it extends from the first cell below the subtotal cell to the first cell above the next subtotal (or the bottom of the sheet). For example, the formula in the first red cell is =SUBTOTAL(9,R[1]C:R[10]C). I need to figure out a way for Excel to know that "10" is the last row of this range. I was thinking some combination of offsets and isblanks or something like that might automatically do it.

    Because things are also happening via code when the user enters a phase number (like the shading being applied), it might also be possible to do this via code when the phase row is added. Many thanks!

    --Karyl

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    This will work if you add the "next" group number at the end of the data (eg put 4 in row 25 of column 1)
    =SUM(INDIRECT(ADDRESS(MATCH(RC1,C1)+1,COLUMN())&": "&ADDRESS(MATCH(RC1+1,C1)-1,COLUMN())))

    If you don't want to add the next group number, it will be much more complicated to check for the ISNA:
    =SUM(INDIRECT(ADDRESS(MATCH(RC1,C1,0)+1,COLUMN())& ":"&ADDRESS(IF(ISNA(MATCH(RC1+1,C1,0)),COUNTA(C5), MATCH(RC1+1,C1,0)-1),COLUMN())))

    If you filled in all the phases (you could use conditional formatting to hide them) you could use the subtotal routine (DAta - subtotals) directly to do this.
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    I tried this out when you first replied, and it seemed to work just fine. Then, today, I finally got around to applying it to more than the first level, and it is creating some type of circular reference. I can't "translate" the formula well enough to figure it out. Could someone take a look at the attached and see if you can see the problem? The first sheet is how I need it to show the subtotals (in red), but uses manually-entered ranges. The second sheet uses the formula Steve provided, but only works correctly for the first level. (I'm assuming it is the circular references that seem to be throwing off the Grand Totals in Row 9 as well, but if not, that may be another problem.)

    And, if someone has the time to write a "translated" version of the formula similar to the one that Hans did for me here <post#=383298>post 383298</post#> , I'd really, really appreciate it. Many thanks!

    --Karyl

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    1) the "numbers" in column A need to be numbers not text. You have formatted them as "Text". If you want to display the ".0" format as numbers with 1 decimal (format - cells).

    Since you have blank rows in col E at the top you must also include an "offset for the blank rows" (your original did not have any blanks).

    Try this:
    =SUM(INDIRECT(ADDRESS(MATCH(RC1,C1,0)+1,COLUMN())& ":"&ADDRESS(IF(ISNA(MATCH(RC1+1,C1,0)),COUNTA(C5)+ 9,MATCH(RC1+1,C1,0)-1),COLUMN())))

    MATCH(RC1,C1,0)+1: looks up the exact value (0) value from col1 in the current row (RC1) in col1 (C1). This gets the row of that number, it then adds 1 to the row. This is the row just underneath the current row.

    COLUMN() is the current column number

    THus:
    ADDRESS(MATCH(RC1,C1,0)+1,COLUMN()) gives the address of the cell directly below the current row in the column.

    MATCH(RC1+1,C1,0) looks up the exact value (0) of 1 + the value from col1 in the current row (RC1+1) in col1 (C1). This gets the row of that "next number"

    ISNA(MATCH(RC1+1,C1,0)) will be true if the "next number is not found" (this occurs for the last number in the series, in your list when the item in col1 = 3 it will look for a 4 and not find it yielding an #N/A error

    COUNTA(C5)+9 counts the number of items in column 5 and adds 9 (to account for the blank rows above it)

    Thus:
    IF(ISNA(MATCH(RC1+1,C1,0)),COUNTA(C5)+9,MATCH(RC1+ 1,C1,0)-1)
    If the row is not found it takes the first item (counta(c5)+9) as the last row number
    otherwise it takes:
    MATCH(RC1+1,C1,0)-1) which is the row above the "next number"

    ADDRESS(IF(ISNA(MATCH(RC1+1,C1,0)),COUNTA(C5)+9,MA TCH(RC1+1,C1,0)-1),COLUMN()))
    Gets the address of the row right before the next number

    INDIRECT(ADDRESS(MATCH(RC1,C1,0)+1,COLUMN())&":"&A DDRESS(IF(ISNA(MATCH(RC1+1,C1,0)),COUNTA(C5)+9,MAT CH(RC1+1,C1,0)-1),COLUMN())))

    Turns the "string" of the address into the "address", whihc is then "summed"

    =SUM(INDIRECT(ADDRESS(MATCH(RC1,C1,0)+1,COLUMN())& ":"&ADDRESS(IF(ISNA(MATCH(RC1+1,C1,0)),COUNTA(C5)+ 9,MATCH(RC1+1,C1,0)-1),COLUMN())))

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    Sorry about sending the original sample sheet without the blanks. I'm STARTING to realize that little details like that are very important. Thanks for the revision based on the new sample. Also, thanks for the great explanation. Whenever I try to figure out one of these lengthly formulas, even one that I wrote, I feel like I'm trying to translate a newly discovered language that no one's figured out yet. Or maybe just one that everyone understands but me!

    I think we have a problem, however. The values in the first column ARE text. In my sample, they just happen to be 1.0, 2.0, etc. They could just as easily be letters or a combination of letters and numbers. The only limitation is number of characters. And because these numbers are sometimes dictated by terms specified in the contract with the client, I can't enforce a different system. So, can we do something similar that's not dependent on the value? I'm too tired tonight to play with it, but knowing how you were using matching and row numbers gives me a chance of maybe figuring something out in the morning. Of course, I'll gladly accept any helpful suggestions you or anyone else might want to share tonight!

    Again, many thanks!

    --Karyl

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    I see a "big" problem, perhaps someone else can prompt a spark:

    Currently given the "1", I know that the "next number" will be 2 (1+1), and if the value is 2 in col 1, then the next will be 3, etc.

    I think the easiest way, might be to do it with the "data- Subtotals" feature of excel. The only thing you need to do is "fill in" all the blanks of col 1 with the value above (the "contextures site" has a simple way to do this manually. You can "hide" these values using confitional formatting. Once you fill in the the blanks, data -subtotals will add the extra rows and add formulas directly as you have them.


    Steve

  7. #7
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    Never mind. Another one of those, "Duh!" moments for me. It dawned on me almost as soon as I sent off the previous message that it had to be the ActiveCell references that were causing the problem. So I rewrote the function as follows:

    <pre>Public Function SumRange(lCurRow As Long, lCurCol As Long) As Currency
    Dim lFirstRow As Long
    Dim lLastRow As Long
    Dim Cell As Range
    Dim x As Long

    'Get the first and last rows from the current cell
    lFirstRow = lCurRow + 1
    Set Cell = Cells(lCurRow, 1).End(xlDown)
    If Cell.Row = "65536" Then
    lLastRow = LastRow()
    Else
    lLastRow = Cell.Row - 1
    End If

    'Get the subtotal
    For Each Cell In Range(Cells(lFirstRow, lCurCol), Cells(lLastRow, lCurCol))
    SumRange = SumRange + Cell.Value
    Next Cell

    End Function</pre>



    It seems to be working fine. New sample attached. Thanks for the "spark" that helped me come up with this, Steve!

    --Karyl

  8. #8
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    READ NEXT POST BEFORE RESPONDING TO THIS ONE. THANKS!

    Thanks for the reference to the Contextures site. I didn't use it in what I'm trying here, but it is definitely something I'll use in the future (and maybe with this, too, if I can't figure out what's wrong).

    I ended up writing a custom function. I tried returning a string to use as the address range for the SUM function, but that didn't work. Using =SUM(SumRange()) when the return value of SumRange was a string like G12:G22 just gave me a #Value! error.

    So, instead, I had the function return the actual sum. And that seems to work just fine--until something happens (not sure what) and every cell with this function changes to the same value. From then on, entering the formula changes ALL the cells the the value of the current cell. It makes sense, sort of. But I need it to stop doing that! Any ideas? Here is the function:

    <pre>Public Function SumRange() As Currency
    Dim lFirstRow As Long
    Dim lLastRow As Long
    Dim lCol As Long
    Dim Cell As Range
    Dim x As Long

    'Get the first and last rows from the current cell
    lCol = ActiveCell.Column
    lFirstRow = ActiveCell.Row + 1
    Set Cell = Cells(ActiveCell.Row, 1).End(xlDown)
    If Cell.Row = "65536" Then
    lLastRow = LastRow()
    Else
    lLastRow = Cell.Row - 1
    End If

    'Get the subtotal
    For Each Cell In Range(Cells(lFirstRow, lCol), Cells(lLastRow, lCol))
    SumRange = SumRange + Cell.Value
    Next Cell

    'MsgBox SumRange

    End Function
    </pre>


    The attached sheet DID have all the correct subtotals when I saved it, but since I don't know what's triggering the recalc, it may not by the time you look at it. And if it does have the right totals (on the second sheet), if you delete the first sheet, it will recalc.

    --Karyl

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    Your code will work as long as there are no blank cells between lCurRow and the last data in the column. If there are any blank cells, the .End(xlDown) will stop at the blank cell. I prefer to use:

    <pre> lLastRow = Range("A65536").End(xlup).Row
    </pre>


    That will always find the last used cell in column A.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    The LastRow function in my SumRange function does that, but only when the code reaches the end of the sheet. Until then, I WANT it to find the blank cells. That's what is distinguishing one group from the next. I wrote my own function because I couldn't figure out a way to subtotal without a constant value to use as the criteria.

    I do still have a problem with the function, however. It seems to be really flaky. It will be working just fine, and then I'll do something else, and when I look back, the totals are either gone or show some really outrageous value. I have to put the cursor in the formula bar and hit Enter on each cell to get the values back to what they are supposed to be. Forcing a recalc. doesn't seem to do it. This is the first function I've written for use on a sheet that has acted this way, but I haven't done very many. And the others were pretty simple and pretty much return the same value throughout the sheet. Maybe there's something you need to do with custom functions that I haven't figured out yet? Any ideas? The workbook I attached earlier may or may not demonstrate the flakiness. But it has the same formulas and data as my sheet that was behaving so strangely. Many thanks!

    --Karyl

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    See if the modifications below make it work less flaky:

    <pre>Option Explicit

    Public Function SumRange(lCurRow As Long, lCurCol As Long) As Currency
    Dim lFirstRow As Long
    Dim lLastRow As Long
    Dim Cell As Range
    Dim x As Long

    'Get the first and last rows from the current cell
    lFirstRow = lCurRow + 1
    Set Cell = Worksheets("Budget Summary Using Formula").Cells(lCurRow, 1).End(xlDown)
    If Cell.Row = "65536" Then
    lLastRow = LastRow()
    Else
    lLastRow = Cell.Row - 1
    End If

    'Get the subtotal
    For Each Cell In Range(Worksheets("Budget Summary Using Formula").Cells(lFirstRow, lCurCol), _
    Worksheets("Budget Summary Using Formula").Cells(lLastRow, lCurCol))
    SumRange = SumRange + Cell.Value
    Next Cell

    'MsgBox SumRange

    End Function

    Public Function LastRow() As Long
    Dim Cell As Range

    'Get the last row in column five
    Set Cell = Worksheets("Budget Summary Using Formula").Cells(Cells.Rows.Count, 5).End(xlUp)
    LastRow = Cell.Row

    End Function
    </pre>

    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Generate Subtotals (Excel 2000/XP)

    Maybe I really AM starting to get this stuff! I woke up this morning with the thought, "I bet I have to reference the specific sheet to make this work right!" Your confirmation came before I had a chance to test it out myself (and helped a bunch because I didn't need to figure out where all I needed to add the reference). Many, many thanks! It seems to be stable, now. (It sure was strange to see the numbers keep jumping all around. . .)

    On to the next thing. . .

    --Karyl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •