suming all specific cell in subsheets

[keyser_Soze]

I have a simple register for my envelope accounting. On the first
sheet, I have
the actual bank register, while on the remaining sheets, I keep track
of 'virtual' registers. I would like to have a specific cell on each
'virtual' sheet which has the ending balance for that sheet, say a7.
On the first sheet, I would like to have the all the other sheet's a7
sumed so I can check against real balance. How can I do that?

Thanks.

 

[George Gee]

You could use a helper column, (on your first sheet)

In Say P1 =Sheet2!A7
In P2 =Sheet3!A7 etc..........

In A7 (on your first sheet) =SUM(P110)


HTH

George Gee

 

[keyser_Soze]

I agree. That would work. However, I add new 'virtual' accounts all
the time and I was hoping there would be a way for them to be picked up
automatically.

Thanks.

 

[Don Guillett]

If you want a sum of all sheets cell a7 use
=sum(firstsheetname:lastsheetname!a7)

 

[keyser_Soze]

How can I change this to something like:

=sum(worksheets(2):worksheets(worksheets.count-1:!a7)

(since the sheetnames can change)

Thanks.

 

[Peo Sjoblom]

That is what Don meant by using 2 empty dummy sheets, if you always insert
the new sheets between
the 2 dummies you can always use the same formula

--
Regards,

Peo Sjoblom

(No private emails please)