Use of Indirect function
 

Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook (using VBA)
and by updating a single cell (again using VBA) all the
SUM formulas will now add through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris

Use of Indirect function
 

Hi Chris;

You can use the paste special method and add the values
without using any functions or formulas. Here is an
example:

Sub try()
Sheets("SourceSheet").Select
Range("A1:B4").Select
Selection.Copy
Sheets("ResultsSheet").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False

End Sub

Thanks,

Greg
-----Original Message-----
Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook (using VBA)
and by updating a single cell (again using VBA) all the
SUM formulas will now add through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris
.

Use of Indirect function
 

Easier is to keep a blank sheet as the last sheet and insert any new sheets
before that, then you can do

=Sum(aaa:last!C1)

and not have to make an adjustment.

--
Regards,
Tom Ogilvy

"Chris Gorham" wrote in message
...
Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook (using VBA)
and by updating a single cell (again using VBA) all the
SUM formulas will now add through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris