# Thread: formulas on 'related' sheets (2002)

1. ## 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. ## 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. ## 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. ## 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. ## 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>

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

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

8. ## Re: formulas on 'related' sheets (2002)

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

Steve

9. ## 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

10. ## 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. ## 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

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

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

14. ## 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. ## 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

#### Posting Permissions

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