suming all specific cell in subsheets

[[email protected]]

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(P1:P10)


HTH

George Gee


[email protected] wrote:
> 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.

[[email protected]]

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.

George Gee wrote:
> 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(P1:P10)
>
>
> HTH
>
> George Gee
>
>
> [email protected] wrote:
> > 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.

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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.
>

[[email protected]]

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)


<[email protected]> wrote in message
news:[email protected]...
> How can I change this to something like:
>
> =sum(worksheets(2):worksheets(worksheets.count-1:!a7)
>
> (since the sheetnames can change)
>
> Thanks.
>