OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Running 'Tally' Across Multiple Sheets

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
clgray
Newbie
Newbie


Joined: 25 Mar 2004
Posts: 4

PostPosted: Thu Mar 25, 2004 12:20 pm    Post subject: Running 'Tally' Across Multiple Sheets Reply with quote

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
View user's profile Send private message Send e-mail AIM Address
Guest






PostPosted: Thu Mar 25, 2004 4:06 pm    Post subject: Re: Running 'Tally' Across Multiple Sheets Reply with quote

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
Newbie


Joined: 25 Mar 2004
Posts: 4

PostPosted: Thu Mar 25, 2004 4:12 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail AIM Address
Guest






PostPosted: Thu Mar 25, 2004 4:21 pm    Post subject: Reply with quote

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
Newbie


Joined: 25 Mar 2004
Posts: 4

PostPosted: Thu Mar 25, 2004 5:06 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail AIM Address
antonbijl
OOo Advocate
OOo Advocate


Joined: 04 Aug 2003
Posts: 291
Location: Pretoria, South Africa

PostPosted: Fri Mar 26, 2004 1:35 am    Post subject: Reply with quote

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
View user's profile Send private message
clgray
Newbie
Newbie


Joined: 25 Mar 2004
Posts: 4

PostPosted: Fri Mar 26, 2004 8:00 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail AIM Address
Guest






PostPosted: Fri Mar 26, 2004 10:50 am    Post subject: Re: Running 'Tally' Across Multiple Sheets Reply with quote

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
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Tue Mar 30, 2004 5:08 am    Post subject: Reply with quote

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
View user's profile Send private message
ssemke
General User
General User


Joined: 30 Mar 2004
Posts: 6

PostPosted: Thu Apr 01, 2004 12:27 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group