Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum cells in multiple worksheets which change every month
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum cells in multiple worksheets which change every month
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? "mnew27" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum cells in multiple worksheets which change every month
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum cells in multiple worksheets which change every month
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 "Tim M" wrote: 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? "mnew27" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I change the names of multiple cells at once? | Excel Discussion (Misc queries) | |||
How can I change text to proper text in multiple cells. | Excel Discussion (Misc queries) | |||
how to change margins for multiple worksheets in Excel? | Excel Worksheet Functions | |||
Cannot change formats of cells??? | Excel Discussion (Misc queries) | |||
How do I retrieve data (specific cells) from multiple worksheets on a shared drive | Excel Worksheet Functions |