| View previous topic :: View next topic |
| Author |
Message |
clgray
Newbie
Joined: 25 Mar 2004
Posts: 4
|
 Posted: Thu Mar 25, 2004 12:20 pm Post subject: Running 'Tally' Across Multiple Sheets |
 |
|
I tried to Google this and search through this lists archives, but I am not even sure how to phrase my question properly...
I am trying to build a Calc file for a client where the first page in the workbook would contain running totals (just basic SUM functions) of the data from each subsequent page. The pages after the first - the 'Totals Page' - would be 'Project Pages'.
I am only looking to track a handful of figures for each project: Rental, Deposits, Security Fees, etc. I just need the 'Totals Page' to be a quick 'at a glance' summary and I need for my client to be able toadd new project pages daily without 'breaking' the formulas.
I vaguely remember something about vertical look-ups from an Excel class I took, but I don't recall being able to add new pages 'at will' without breaking the formulas.
Any help GREATLY appreciated, thanks!
Chris G. |
|
| Back to top |
|
 |
Guest
|
| Posted: Thu Mar 25, 2004 4:06 pm Post subject: Re: Running 'Tally' Across Multiple Sheets |
|
|
| clgray wrote: | I am trying to build a Calc file for a client...........
Chris G. |
Hi,
You are being paid for this?
David. |
|
| Back to top |
|
|
clgray
Newbie
Joined: 25 Mar 2004
Posts: 4
|
| Posted: Thu Mar 25, 2004 4:12 pm Post subject: |
|
|
Ummm... yeah... why?
I - as a general rule - get paid for everything I do and - no - that doesn't offend or violate any 'open source' ethic. I've been getting paid for open source solutions of one kind or another for almost a decade.
Chris G. |
|
| Back to top |
|
|
Guest
|
| Posted: Thu Mar 25, 2004 4:21 pm Post subject: |
|
|
| clgray wrote: | Ummm... yeah... why?
I - as a general rule - get paid for everything I do and - no - that doesn't offend or violate any 'open source' ethic. I've been getting paid for open source solutions of one kind or another for almost a decade.
Chris G. |
It's not the Open Source ethic that I wondered about. You are getting paid to do this spreadsheet, then, no offense, but I only wish you well in your endeavour and hope that you are paid handsomely for it.
David. |
|
| Back to top |
|
|
clgray
Newbie
Joined: 25 Mar 2004
Posts: 4
|
| Posted: Thu Mar 25, 2004 5:06 pm Post subject: |
|
|
Ah... sorry if I sounded defensive - I have seen flame wars before over charging for Open Source stuff. I am a web developer by trade but clients always consider you the 'computer guy' if you do any type of work for them and bring you ever project that comes thier way.
Chris G. |
|
| Back to top |
|
|
antonbijl
OOo Advocate
Joined: 04 Aug 2003
Posts: 291
Location: Pretoria, South Africa
|
| Posted: Fri Mar 26, 2004 1:35 am Post subject: |
|
|
Chris
Getting back to the question at hand...
I don't think there's an easy way. If your client is going to be adding new sheets on a regular basis and expect these to be included in your summary page formulae then it could get tricky. I don't know of any way to make a formula apply say to do a lookup on all subsequent sheets or anything like that.
What I would suggest is that you write a macro for adding new 'Project sheets', so that the client doesn't add them manually, but uses the macro instead. You can then add code in the macro to change all the formulae in your summary sheet to include the new sheet.
There you go. Sorry I can't give you any more detail (haven't had a lot of time to play around with macros in OOo, but it isn't too difficult (tried once and easily managed to do what I was attempting)) but I'm sure someone else can and to my mind it is the only way you could achieve this.
Cheers
Anton |
|
| Back to top |
|
|
clgray
Newbie
Joined: 25 Mar 2004
Posts: 4
|
| Posted: Fri Mar 26, 2004 8:00 am Post subject: |
|
|
I am still investigating all the options. I am told in Gnumeric that there is something called '3D References' that will tally all cells in a range between the first and last sheets in a workbook. I want to see if something similar exists in OO.
Thanks for your kind help!
Chris |
|
| Back to top |
|
|
Guest
|
| Posted: Fri Mar 26, 2004 10:50 am Post subject: Re: Running 'Tally' Across Multiple Sheets |
|
|
| clgray wrote: |
I am trying to build a Calc file for a client where the first page in the workbook would contain running totals (just basic SUM functions) of the data from each subsequent page. The pages after the first - the 'Totals Page' - would be 'Project Pages'.
I vaguely remember something about vertical look-ups from an Excel class I took, but I don't recall being able to add new pages 'at will' without breaking the formulas.
Any help GREATLY appreciated, thanks!
Chris G. |
OK, the way I see it:
Subsequent pages are necessary because the client can not foresee future needs? that seems to be an impossible situation when trying to devise a general formula. If you knew the expected structure of a future page, you could simply include those with empty cells, since you have indicate that any values are simply added. The empty cells can still be referenced in a SUM on the first page. A formula would not be "broken", but would already be extended to include some future possibility; a blank cell in a later page reference. If you have no idea of an expected future structure of a new page ...??????
Still a bit vague on the intent; I'm missing something perhaps. If the client is capable of manufacturing the pages, then he could do it at first, you'd think. If not, then he needs to call you each time for some new project (page.) Or, each page has some standard format that can simply be copied, and again, blanks could have been included in the formulas (addition) on the first page.
I still don't see the reference to vertical lookups in this instance.
P.S. Forgive my initial reluctance. I've seen similar requests from students intent on someone doing their project for them. ["Skim milk often masquerades as cream." Ogden Nash.]
David. |
|
| Back to top |
|
|
nom
OOo Enthusiast
Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia
|
| Posted: Tue Mar 30, 2004 5:08 am Post subject: |
|
|
If all the sheets are similar than this is easy to do.
Create a templeate sheet that is invisible.
Then when your customer wants to a new sheet they simply click an "add sheet" button, whihc would be linked to a macro wihich would copy the template sheet and place it at the back of all the sheets and set it's visibility to true.
You can then using a macro determine the number of sheets in a work book and loop through all of them while you are adding the cells you need at the same time.
I have done this and it works but it can get slow depending on the number of sheets and calculations that need to be performed.
I too wish there was some sort of a 3D sum function in calc.
Cheers,
Nom |
|
| Back to top |
|
|
ssemke
General User
Joined: 30 Mar 2004
Posts: 6
|
| Posted: Thu Apr 01, 2004 12:27 pm Post subject: |
|
|
| Quote: | | I am trying to build a Calc file for a client where the first page in the workbook would contain running totals (just basic SUM functions) of the data from each subsequent page. The pages after the first - the 'Totals Page' - would be 'Project Pages'. |
A clunky method I've used in the past is to sandwich all 'Project Pages' between a 'start' page and an 'end' page (thus sheets would be ordered like "summary", "start", "project1", "project2", "project...", "end"), then formulae on the summary page would look similar to this:
=SUM($start.A1:$end.A1)
As long as you leave the start and end pages blank and your careful to insert project pages only between start and end pages, you're good to go.
ssemke |
|
| Back to top |
|
|
|
