| View previous topic :: View next topic |
| Author |
Message |
tribsm
Newbie
Joined: 29 Dec 2006
Posts: 2
Location: South Africa
|
 Posted: Fri Dec 29, 2006 8:58 pm Post subject: LINKING SHEETS |
 |
|
| 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 |
|
 |
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
| Posted: Fri Dec 29, 2006 9:53 pm Post subject: |
|
|
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 |
|
|
Warrick Nelson
OOo Advocate
Joined: 04 May 2004
Posts: 203
Location: Christchurch, New Zealand
|
| Posted: Sat Dec 30, 2006 2:00 pm Post subject: |
|
|
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:F /5)=0;"";SUM(Sheet1.B8:F/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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Dec 30, 2006 3:20 pm Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
tribsm
Newbie
Joined: 29 Dec 2006
Posts: 2
Location: South Africa
|
| Posted: Tue Jan 02, 2007 5:12 am Post subject: |
|
|
| 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 |
|
|
geoff80fg
OOo Advocate
Joined: 26 Jul 2006
Posts: 420
Location: UK
|
| Posted: Tue Jan 02, 2007 6:11 am Post subject: |
|
|
| 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 |
|
|
|
