Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    formulas on 'related' sheets (2002)

    This may not have been the way to set up this application, but I've inherited it and it's too large to change the approach now.
    On a "data summary" sheet, the user wants to be able to collect some information, including calculations (e.g., some totals, some counts, etc.) that comes from sheets that are related in the following way. Each sheet is named "0001", "0002", "0003", etc. Other sheets are appended to the workbook in exactly the same format as these (they all have the same layout) and are called "0001-1", "0001-2", "0003-1" and so on. As you might expect, allsheet tabs with names beginning with "0001" are related. A calculation might be something like =sum('0001'!B2:B12,'0001-1'!B2:B12, etc.). Any thoughts on how to do this (I suspect writing the formulas using VBA, but I'm a serious novice at that...maybe a sample would get me started in doing other formulas in parallel to the sample)? Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas on 'related' sheets (2002)

    If I understand your question correctly, you can copy the following function into a module in your workbook:

    Public Function RSum(strSheet As String, strRange As String) As Double
    Dim wsh As Worksheet
    Application.Volatile
    For Each wsh In ActiveWorkbook.Worksheets
    If Left(wsh.Name, Len(strSheet)) = strSheet Then
    RSum = RSum + Val(wsh.Range(strRange))
    End If
    Next wsh
    End Function

    You can use a formula such as

    =RSum("0001","B1:B12")

    in a cell to sum cells B1:B12 over all sheets whose name begins with 0001.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: formulas on 'related' sheets (2002)

    Thanks, again, Hans. I copied the user function into the module, but it seems to only work with single cell references and not ranges.
    With a range, I get #VALUE! error.

  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: formulas on 'related' sheets (2002)

    Instead of:
    RSum = RSum + val(wsh.Range(strRange))

    Try:
    RSum = RSum + Application.WorksheetFunction.Sum(wsh.Range(strRan ge))

    Steve

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

    Re: formulas on 'related' sheets (2002)

    Try this modification to Hans' function:

    <pre>Public Function RSum(strSheet As String, strRange As String) As Double
    Dim wsh As Worksheet, oCell As Range
    Application.Volatile
    For Each wsh In ActiveWorkbook.Worksheets
    If Left(wsh.Name, Len(strSheet)) = strSheet Then
    For Each oCell In wsh.Range(strRange)
    RSum = RSum + Val(oCell)
    Next oCell
    End If
    Next wsh
    End Function
    </pre>

    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas on 'related' sheets (2002)

    Oops, insufficient testing. Sorry about that. Change the line RSum = RSum + ... to

    RSum = RSum + Application.WorksheetFunction.Sum(wsh.Range(strRan ge))

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas on 'related' sheets (2002)

    Thanks! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    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: formulas on 'related' sheets (2002)

    Great minds... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formulas on 'related' sheets (2002)

    Hi,

    Maybe I've missed something, but why not use a 3D formula? For example:
    =SUM('0001:0001-4'!C163:E163)
    would add up all of the values in cells C163-E163 on the series of sheets from '0001' to '0001-4'. The only provisio for this to work is that the tabs for all of the sheets to be processed have to be between the two anchor sheets.

    Avoids macro activation/warning issues and is much easier for most people to follow (once they understand the 3D concept).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: formulas on 'related' sheets (2002)

    I wish it were that easy...but the sheets that relate aren't contiguous, so the user function (revised) did the trick.
    I even understand the VB...now have to modify it for other types of calculations. Thanks to all for the quick solution!!

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formulas on 'related' sheets (2002)

    Hi,

    Although the sheets might not be contiguous/sequential now, re-ordering would soon fix that. Worth considering if you're able to adopt such a structure.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: formulas on 'related' sheets (2002)

    I did think about reordering them...then, my snag is knowing how many tabs fit into each related category. It's not always the same.
    0001,0001-1, 0001-2; 0002, 0002-1; 0003; 0004, 0004-1, 0004-2; etc.

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

    Re: formulas on 'related' sheets (2002)

    And what happens when sheet 0001-5 gets added in order?
    Legare Coleman

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: formulas on 'related' sheets (2002)

    There is a "summary" sheet at the beginning of the workbook. The user enters the "master" account (tab name) such as 0003. Then, various calculations are performed that use that sheet (0003) and any and all related to it (those with 0003-n as the tab name). So, the user defined function needs to find all of the sheets with the first four digits being 0003 (in this example). So, even if there were only 0003, 0003-1, and 0003-2 and the sheets were sorted, when a new sheet is added to the end of the workbook and it's 0003-3, even it the sheets are ordered, I don't know how I would tell how far to look...sometimes it would be (only) 0003, then another time it would be 0003, 0003-1, 0003-2; then, there could be any number of additional tabs at yet another time. I think the previously-describe macro will do the trick for me.

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formulas on 'related' sheets (2002)

    The only reliable way I can think of using the 3D formula with sheets being added/deleted is to have an empty 'anchor' sheet for each series, in addition to your 'master' sheet. Thus, you might have sheets 0003, 0003-1, and 0003-x to start with, and the 3D formula would be something along the lines of =SUM(0003:0003-x!A1). Then, as you add more sheets to (or delete sheets from) the '0003' series (eg add 0003-2, 0003-3), the formula would automatically update the result. A possible bonus is that, if you want to see the effect withou one of the sheets, all you need to do is to move that sheet's tab outside the source range (eg to just after sheet 0003-x).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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