Algorithm Help

[aMember]

Here's the problem:

I have a workbook with two hidden sheets named SUB and COMP.
SUB and COMP work together. Through button in the workbook, SUB and COMP can be copied and renamed to SUBn and COMPn. Each SUBn sheet can have many COMPn sheets but each COMPn sheet "belongs" to only one SUB sheet. Any time a SUB is created, SnC1 must be created also. (eg example 1)
So, through a push button and dialog box, the user created first SUB1, which automatically created S1C1. In example 2, the user created SUB2 and they automatically got S2C1. But, they used the push button again to create S2C2 and S2C3.

(S means SUB, C means COMP):

SUB1, S1C1
SUB2, S2C1, S2C2, S2C3
SUB3, S3C1, S3C2

There are tables on the SUB sheet that sum all the tables on the SnCm sheets. For example, cell B10 on SUB1 (named BaselineNew) sums cell B10 on S1C1.
Cell B10 on SUB2 needs to sum cell B10 on sheets S2C1, S2C2, and S2C3.
Likewise, cell B10 on SUB3 needs to be the sum of cell B10 on sheets S3C1, S3C2.

I have tried this a variety of ways but cannot get it to work. Using example 2, my most recent attempt was when S2C1 was created, I created a named range for cell B10 on that sheet. When S2C2 was created, I extended the named range to include cell B10 on this sheet. The formula for SUB2 was

=sum(CompBaselineNew)

But, the named ranges are not working out like I want them to.

Any help or guidance would be greatly appreciated.
Andrea

 

[RobBroekhuis]

How about every time you create a new comp sheet, you just re-assign all the formulas on the corresponding sub sheet? Sound like a lot of work, but it doesn't have to be. Something like

sub UpdateSheet(subnr as integer, compnr as integer)
dim cell as range, i as integer, s as string
for each cell in sheets("SUB" & subnr).cells.specialcells(xlCellTypeFormulas)
s="=S" & subnr & "C1!" & cell.address
for i=2 to compnr
s=s+"+S" & subnr & "C" & i & "!" & cell.address
next i
cell.formula=s
next cell
end sub

Rob