PDA

View Full Version : Excel Question-can anyone help?



smurf
20-10-2009, 12:09 PM
I have a workbook consisting of several sheets, each sheet representing a financial record. There probably 10 or so of these. The last page I have set up to summarise the dollar value (Total) represented on each of the sheets. What I am doing at the moment is transferring the individual balance of each sheet from that sheet to the summary page. What I would like to know however, is there a way of automatically transferring each of the individual balances on each respective sheet to the summary page and if so how to set it up:thanks

B.M.
20-10-2009, 12:46 PM
Well if I’m reading this right you can enter a formula ia a cell on the summary page which says:

=SUM(Sheet1!A20+Sheet2!A21+Sheet3!A15)

To save writing the whole lot just click in the cells you want to add but don’t forget to add the + sign before changing pages.

smurf
21-10-2009, 10:43 AM
Thanks BM......it didn't work so I guess it is back to manually entering. :)

kahawai chaser
21-10-2009, 12:04 PM
You could try consolidated tables, where the summary table can be auto updated. But I think the tables require the same structure/setup; Tutorial at Microsoft... (http://support.microsoft.com/kb/214270)

stormdragon
21-10-2009, 12:07 PM
Is there a cell on each sheet with the total for that sheet or do you want to add multiple items from each sheet into the summary?

smurf
21-10-2009, 07:44 PM
kakawai chaser. Thank you. Stormdragon, thanks for your reply too. The book is a Cash book with each page(sheet) representing and area of expenditure. The cell representing the changing balance for that area of expenditure is the one I want to transfer to a sheet which I call a summary of the individual balances on the various sheets. Therefore if the cashbook say has ten sheets each with its own balance which will change as deposits or withdrawals are made, it is that balance along with that of the other 9 sheets that I want to transfer to a list on the summary page so that I can see a grand total balance of my cashbook which I can then reconcile with my bank accounts. I hope this makes sense:)

Sweep
21-10-2009, 07:54 PM
B.M. is on the rght track here but what you wqant is to make the references absoute by using the $ symbol:
=SUM(Sheet1!$A20+Sheet2!$A21+Sheet3!$A15)

The actual cell reference you want from each sheet is the final balance.

smurf
21-10-2009, 08:08 PM
Thank you Sweep I will try that :)

johnd
21-10-2009, 08:18 PM
Just want to make sure that you know that you can go "=sum(" then navigate to the sheet and cell you want, click on the cell then push + ..etc.

smurf
21-10-2009, 08:30 PM
Thank you johnd I didn't know that so thank you for telling me :)

smurf
21-10-2009, 08:48 PM
Well guys thank you for your help I have managed to get it to work to a point. Obviously the reference cell that you point to in the formula will change with the next transactional balance on each sheet. Is there any way that I can get it to recognise that reference cell change automatically or will I have to manually do it?

johnd
21-10-2009, 09:21 PM
Is there any way that I can get it to recognise that reference cell change automatically or will I have to manually do it?

I think that if you name the cells you are adding instead of using cell references it should work.

smurf
21-10-2009, 09:37 PM
Not sure I understand what you mean eg at the moment say the balances are in column F and the balance at the moment is say F27, the next one will be F28 and so on down the column each time I revise the balance. How would I express that in the formula?

Sweep
21-10-2009, 09:38 PM
Well guys thank you for your help I have managed to get it to work to a point. Obviously the reference cell that you point to in the formula will change with the next transactional balance on each sheet. Is there any way that I can get it to recognise that reference cell change automatically or will I have to manually do it?

So can I take it that each sheet grows in a verticle direction on a regular basis? If so then figure the maximum number of rows any particular sheet is likely to grow to and use the bottom row to contain the balance.

That way you can use the absolute method as already decribed.
Possibly the whole workbook needs redesigning anyway.

smurf
21-10-2009, 09:46 PM
Hi Sweep
Yes, each sheet grows in a vertical way on a regular basis. Therefore the balance updates with each entry. I guess you are right in that the workbook needs redesigning but that's what you get when a very raw amateur starts out playing :)

smurf
21-10-2009, 10:03 PM
Sweep....it is on its way....Thanks a zillion :)