Putting a total on front page of excel sheet

[debful]

Hi All, I have an excel spreadsheet that on the 1st worksheet I have totals. I have other worksheets that have running balances on them. It is a budget spreadsheet and the front page has how much is left in the overall budget while the other sheets have each budget amount eg furniture. I want the front page to update how much is left overall in the whole budget. Any ideas on how I can do this?

Cheers
Debbie

 

[dgillz]

Um...a formula?

Not trying to be a smartass but isn't this obvious? If you have tried a formula and ir isn't working, let's explore why it isn't working. You have not given us too much to go on at this point.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[debful]

Hi, sorry I haven't made it that clear. What I want is to put a figure into worksheet 2 and have the balance appear on worksheet 1. Eg worksheet 1 has on it Furniture $2000 and the balance that is left in that account say $1000. Worksheet 2 is for Furniture and it has the orginal amount of $2000 plus listed the amounts we have spent eg chairs $1000 thus the balance is $1000. As I add a new item into worksheet 2 Furniture account Say we spend another $500 leaving only $500 in the furniture account, then on worksheet 1 (front page) I want it to now show only $500 left in the furniture account eg a summary of the other worksheets balances. There are 10 worksheets involved. I have used Link but if I add more lines into the worksheet, the Link only works for the actual cell I have chosen. Hope this makes more sense! Sorry but hard to explain. Cheers

 

[SkipVought]

I have used Link but if I add more lines into the worksheet, the Link only works for the actual cell I have chosen.

No, you have not been clear. FIRST you refer to PAGES. Then to refer to WORKSHEETS.

Aggregations work on ranges. I do not understand whay you state, "the Link only works for the actual cell I have chosen." If you were using Dynamic Named Ranges (faq68-1331) this would not be a probelm.

I also see a problem with the fact that "There are 10 worksheets involved," on of which is the "Furniture" account. You have shot yourself in the foot, by chopping up your data into 10 different sheets. If ALL you accounts were in ONE SHEET (one table) it would make your life so much easier. Excel is no designed to work this way.

So...

1. get all your data into one sheet

2. use dymanic hamed ranges to reference your aggregations

Having one consolidated table, you could also use the PivotTable wizard to perforn your aggregations. I hope you have also looked at the Subtotal Wizard.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Worksheet 2 is for Furniture and it has the orginal amount of $2000 plus listed the amounts we have spent eg chairs $1000 thus the balance is $1000

I have used Link but if I add more lines into the worksheet, the Link only works for the actual cell I have chosen.

What formula have you used?
Are the worksheets all in the same workbook?

In Worksheet2 if the only numbers you have in column A are the cost of purchases so far then in Summary sheet:
=SUM(Sheet2!A:A)
will give the cumulative total mount spent.
=SUM(Sheet2!A3:A65535)
will give the sum of the cells specified.

It sounds though as if sheet2 already has the balance remaining. So if that is in cell A57 say then =Sheet2!A57 would give your answer. If all sheets are in the same workbook then inserting a row above A57 will result in the formula automatically adjusting to point to A58. If in different workbooks then use a named range "FurnitureBalance"


Gavin

 

[debful]

Dear Skip, sorry for not being perfect!
"No, you have not been clear. FIRST you refer to PAGES. Then to refer to WORKSHEETS." My bad. Pages and Worksheets same thing.
Boss wants separate WORKSHEETS for each item with the first WORKSHEET having the totals of what is left in each of the other WORKSHEETS. Boss has less idea than I do so I can't hide columns to show her just the total as she wants to see what the money has been spent on. I did the same thing last year but spread it all out on 1 worksheet but she said it wasn't clear and only wanted to see the totals unless she needed to know what the money was used for.
"use dymanic hamed ranges to reference your aggregations" have no idea what this even means. I think this is far too technical for me. I may go back to writing it out by hand!

 

[SkipVought]

I was not suggesting anything else than putting all the aggregations on a summary sheet. That is a good idea. My point was, that it can MOST EASILY be accomlished, if ALL your budget data were in ONE TABLE on ONE SHEET. Bit it certainly can be done with 10 sheets. It just makes your life a lot more complex, hence the shoot-in-the-foot remark.

Are you using any formula as dgillz originally suggested?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Skip's suggestion of one data sheet plus pivot tables could do what your boss wants. One Pivot table for the summary and one generic table for account detail. Set Account as a 'page field' then use the 'show pages' command to automatically create a sheet for each account.

I think this is far too technical for me. I may go back to writing it out by hand

In that case I would suggest forgetting pivot tables and trying to get all the data into one sheet.
If you want us to help you learn then tell us exactly where you need help. The more specific you are the more specific the suggestions can be (and therefore the easier for you to understand).
1. How are the account sheets structured?
2. What formula have you tried?
3. Are the worksheets all in the same workbook?

Gavin

 

[SkipVought]

"use dymanic hamed ranges to reference your aggregations" have no idea what this even means. I think this is far too technical for me. I may go back to writing it out by hand!

This is NOT "too technical" for an average Excel user. It may be, if you're not willing to educate yourself in the fundamentals of Excel, which, it seems from all your posts, that you may be somewhat lacking. But that, in itself, is not a terminal condition. Refusing to better yourself IS.

As Gavin stated, we are here to help you, if you want to be helped.

Ball's in your court.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}