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

LINKING SHEETS

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


Joined: 29 Dec 2006
Posts: 2
Location: South Africa

PostPosted: Fri Dec 29, 2006 8:58 pm    Post subject: LINKING SHEETS Reply with quote

I have entered performance figures in Sheet1 B5:F5 and every 2 weeks I will be adding more data e.g. B6:F6 and so on. I now want to perform some calculations, but I want their results to be automatically updated in Sheet2. For example, in sheet2 under my heading "average" I want to display the average of cells B5"F5 in sheet1. How do I write the formula to link sheets?
Back to top
View user's profile Send private message Send e-mail
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Dec 29, 2006 9:53 pm    Post subject: Reply with quote

It's a fair question. On my reading of Help for "references;to cells", there seems to be no mention of cells on another sheet.

Some diligent person with the latest version could file an issue.

Refer to your cells as Sheet1.B5:F5

Remember that you need to use the prefix $ for any reference you wish to make absolute. That is mentioned in Help under the heading I've quoted.
_________________
search forum by month
Back to top
View user's profile Send private message
Warrick Nelson
OOo Advocate
OOo Advocate


Joined: 04 May 2004
Posts: 203
Location: Christchurch, New Zealand

PostPosted: Sat Dec 30, 2006 2:00 pm    Post subject: Reply with quote

Enter the data as described in Sheet1.

In sheet2, I clicked on cell B5, the the Sum command (sigma symbol), then click on the Sheet1 tab and select cells B5..F5. Hit enter.

Sheet2 B5 will contain =SUM(Sheet1.B5:F5) add /5 to get the average.
Copy this cell down to get the other rows.

I'm guessing Sheet1 A5 will have a date or week designation. In Sheet2 A5, press = then click on Sheet1 and A5, then enter. Sheet2 A5 will contain =Sheet1.A5 which you might need to format as date.

This direct selection of cells for a formula is a lot easier than typing in the full formula, especially when the tabs have long names (I like to have then descriptive of thier content, not the default Sheet1 etc).

I usually take this sort of sheet and fill it as far as needed with formulae, but then want to suppress 0 answers. Use =IF((SUM(Sheet1.B8:FCool/5)=0;"";SUM(Sheet1.B8:FCool/5)

I would also put a comment on Sheet2 to warn to check formulae reach the bottom of the rows on Sheet1. Little notes to help users when they come back to the sheet save a lot of time (and I include myself as one of these users)!
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Dec 30, 2006 3:20 pm    Post subject: Reply with quote

You want to calculate Average(B5:F5) and next time, when you have entered data below row 5, the very same formula should calculate average(B6:F6)?
You need one coumn with consecutive values (no gaps with blank cells). Let's say sheet2, column A.
=AVERAGE(OFFSET($Sheet2.$B$1:$F1;COUNTA($Sheet2.$A$1:$A$65536)-1))
Leave out the "-1" if sheet2 has a header row.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
tribsm
Newbie
Newbie


Joined: 29 Dec 2006
Posts: 2
Location: South Africa

PostPosted: Tue Jan 02, 2007 5:12 am    Post subject: Reply with quote

Thanks guys, your help is most valuable. Now, if I may ask another question, which command in Officeorg does the same as autofit in Exel?
Back to top
View user's profile Send private message Send e-mail
geoff80fg
OOo Advocate
OOo Advocate


Joined: 26 Jul 2006
Posts: 420
Location: UK

PostPosted: Tue Jan 02, 2007 6:11 am    Post subject: Reply with quote

noranthon wrote:
It's a fair question. On my reading of Help for "references;to cells", there seems to be no mention of cells on another sheet.


In version 2.1, looking up "references > to cells in another document" brings up "Referencing Other Sheets". Not exactly other documents - so easy to overlook. You've got to dig deep(ly) in Help references!

Geoff
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