Sum cells in multiple worksheets which change every month

[mnew27]

Hi, I have multiple worksheets, each tracking an individual's sale and
purchase of shares. There is a column on each worksheet adding up the total
shareholding for that individual, as trades occur, the list grows. e.g.

A B C
D
1 Date Opening Balance Bought/Sold Closing Balance
2 4 May 06 200 10
210
3 5 May 06 210 -5
205

My problem is that I want to summarise the total shareholdings (closing
balances) for each individual in a table on a separate sheet, but I don't
know how to do this when the figure I need isn't in the same cell each time.
For example on 4th May I need to total the amounts in cell D2, the next day I
need to sum the amounts in D3. Another complication is that the figures I
need to sum aren't in the same column on each worksheet. Any ideas?

 

[Tim M]

Couple of questions, each sheet is named after the individual?
How do you have this data structured? Only dates are displayed if the
closing balance changes I assume?

Do you make your opening balance = the closing balance of the entry above it?

 

[Bob Phillips]

I would suggest that you get some structure and order into your worksheets,
or you will forever be chasing a moving target. Clever design is better than
clever coding.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[mnew27]

Hi Tim M

Yes, the worksheets are named after the individual. Each individual can
have more than one account that I need to track and so is something like:

Column A always contains the transaction date, so

A1 = Transaction date
B1 = First Account to be tracked
B2 = Opening Balance
C2 = Qty Bought/Sold
D2 = Closing Balance
E1 = 2nd account to be tracked
E2 = Opening Balance
F2 = Qty Bought/Sold
G2 = Closing Balance
H2 = Sum of C3 + C6

I then have a summary sheet with all the individuals where I manually cut
and past the total in H2, but it might not be the value in H2 on another
worksheet as some may not have done as many transactions. It's this manual
cutting and pasting of the total shares each individual has that I'd like to
try to automate, but I don't know how I can with the totals being in
different rows, and sometimes different columns depending on how many
accounts the individual has.

Any help you could give would be really appreciated.
Thanks in advance.
M