LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Distributions > SUSE / openSUSE
User Name
Password
SUSE / openSUSE This Forum is for the discussion of Suse Linux.

Notices


Reply
  Search this Thread
Old 06-26-2006, 04:41 AM   #1
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Rep: Reputation: 31
OpenOffice.org calc problem


Hi,

I have a problem with calculations with OO calc. The problem is as follows:

In first spreadsheet I have a numbers in cells A1:A4 and I calculate a SUM of this four numbers(let us say A1 = 1, A2 = 2, A3 = 3 and A4 = 4)in cell A5.
Then I transfer the cells from A1 to A4 into second spreadsheet and also I transfer the SUM ino spreadsheet 2. The result I get in spreadsheet 2 is 0 instead of 10.???? The same calculation works in excel in wins.

What I did wrong?
 
Old 06-27-2006, 12:36 AM   #2
GrueMaster
Member
 
Registered: Aug 2005
Location: Oregon
Distribution: Kubuntu.
Posts: 848

Rep: Reputation: 30
Exactly how did you transfer the cells? Copy (<ctrl>-c) and paste (<ctrl>-v) worked for me just fine, starting exactly as you described. Are you using a different method to copy the numbers?

Check your formula in cell a5 of the new sheet. Where does it point to?
 
Old 06-27-2006, 01:09 AM   #3
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Original Poster
Rep: Reputation: 31
Hi GrueMaster,

no I did not copy the cell. I point to the cell I want to have in the second sheet. It does not matter whether it is a formula or just a text. I always get 0 as the result. I hope you understand me.
 
Old 06-27-2006, 01:28 AM   #4
GrueMaster
Member
 
Registered: Aug 2005
Location: Oregon
Distribution: Kubuntu.
Posts: 848

Rep: Reputation: 30
I guess I don't understand. Here's what I tried:

Open Calc (defaults to new .ods file with 3 sheets or tabs).

First sheet (sheet1):
A1: 1
A2: 2
A3: 3
A4: 4
A5: =sum(a1:a4) - 10

Second Sheet (sheet2 - same file):
Cell | Result
A1: =Sheet1.A1 | 1
A2: =Sheet1.A2 | 2
A3: =Sheet1.A3 | 3
A4: =Sheet1.A4 | 4
A5: =sum(a1:a4) | 10


This works for me also. Maybe you could send me a step by step procedure to recreate this?
 
Old 06-27-2006, 01:44 AM   #5
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Original Poster
Rep: Reputation: 31
Hi,

No, you have to open a new document, not a new worksheet in the same doc. and then just point to any cell in the previous doc. you want to have in the second document. Important is that you have two different windows open and try to connect the cells (any cell)
doc 2, cell B3 point to doc 1, cell A5 and what you get is 0. Write something into a cell in doc 1, open doc 2 and go to any cell (b 3) in this doc, then = (point to cell A5) and what you get is 0, not what you have in A5 really.

(Sorry i just cant express myself in English better than that.)
regards
 
Old 06-27-2006, 09:09 AM   #6
abisko00
Senior Member
 
Registered: Mar 2004
Location: Munich
Distribution: Ubuntu
Posts: 3,517

Rep: Reputation: 58
How did you connect the cells?

Here's what I did (and it works to my surprise):
  • Create doc1.ods with cells A1=1; A2=2; A3=3; A4=4
  • Save doc1.ods
  • Copy cells A1:A4 from doc1.ods
  • Create doc2.ods
  • Paste Special -> Link
  • The formula will look like this: {=DDE("soffice";"/home/user/doc1.ods";"Sheet1.A1:A4")}
  • In cell A5 of doc2.ods enter =SUM(A1:A4)
  • Change a value in doc1.ods and see how it changes in doc2.ods
 
Old 06-27-2006, 11:14 AM   #7
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Original Poster
Rep: Reputation: 31
Hi abisko00,

look, the problem I have exist only in OpenOffice calc. In Windows excel it works. I will try to explain.

I have a document (price list) which depends on many variables. The most simple one is as follows:

I open a document No.1 with many cells, with many calculations. Then There is another document with other data which depends on the changes made in first document. I have to point to, let us say cell A5 and when the value in that cell changes the value in the second document changes too. As I said if I have a number in doc 1, cell A5 and I want to point from second document cell B3 to the first cell I get 0 instead of the value written there.
 
Old 06-27-2006, 03:04 PM   #8
Bugrem
Member
 
Registered: Nov 2004
Distribution: Suse 9.3, 10.0, 10.1, 10.3
Posts: 65

Rep: Reputation: 16
Hi.

As abiskoOO says, you have to link, not copy between the various cells in the two spreadsheets. You can use named ranges if this makes it any easier.
It works in exactly the same way as Excel.

HTH
B.
 
Old 06-27-2006, 11:18 PM   #9
GrueMaster
Member
 
Registered: Aug 2005
Location: Oregon
Distribution: Kubuntu.
Posts: 848

Rep: Reputation: 30
It might help to know what version you are using. I'm running Openoffice 2.02.

I tried it with the paste special ->link trick, and the only problem I had was when I changed the name of the originating sheet.
 
Old 06-28-2006, 01:18 AM   #10
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Original Poster
Rep: Reputation: 31
Hi bugrem, hi GrueMaster,

I really do not know what I am doing wrong. The same thing is working in excel in Windows. As I said I am not copying I just link the two cells from both docs ( I mean that I write in doc2 cell A5 " = and then point with the mouse cursor to the cell A5 in doc1.
I linked cell A5 in doc2 with cell A5 in doc1.
When I link them I get this in doc2:
='file:///home/bgorjup/doc1.ods'#'Delovni list1'.A5
I get just A5 from doc1, not the formula

 
Old 06-28-2006, 01:37 AM   #11
GrueMaster
Member
 
Registered: Aug 2005
Location: Oregon
Distribution: Kubuntu.
Posts: 848

Rep: Reputation: 30
What you get in doc2 is the same thing I get. It is a pointer to the value contained in doc1, cell A5. Doc1, A5 may be a formula itsself, but doc2 doesn't need to know that. Maybe you need to turn on autocalculate for Doc2? It's in Tools->Cell Contents->AutoCalculate.

If you only wanted the formula from Doc1:A5, just do a normal copy and paste (<ctrl>-c, <ctrl>-v).
 
Old 06-28-2006, 04:25 AM   #12
abisko00
Senior Member
 
Registered: Mar 2004
Location: Munich
Distribution: Ubuntu
Posts: 3,517

Rep: Reputation: 58
Quote:
look, the problem I have exist only in OpenOffice calc. In Windows excel it works. I will try to explain.
Have you tried as I said above? It works perfectly in OpenOffice (I wasn't talking about excel!).

Excel is not OpenOffice, so don't expect that everything works exactly the same way (I still haven't found how to assign asymetric error bars in OpenOffice).
Quote:
As I said if I have a number in doc 1, cell A5 and I want to point from second document cell B3 to the first cell I get 0 instead of the value written there.
You want the content of A5 in doc1 appearing as B3 in doc2, right?

Copy (Ctrl+C) cell A5 in doc1 (doesn't matter if it's a value or a formula), change to doc2, right-click on B3 and select "Paste Special", then "Link". From now on, the content of A5 in doc1 will automatically appear in B3 of doc2.

Last edited by abisko00; 06-28-2006 at 04:33 AM.
 
Old 06-28-2006, 05:13 AM   #13
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Original Poster
Rep: Reputation: 31
Hi guys,

Thank you all for help. Yes abisko00. I did as you told. It works, at least, it works with this simple calculations. I will try wit my document which i made in windows excel before I swap to Linux. It is a sort of price list with many sheets and many dependent calculations. This is a calculation for a machine, where the end price changes when i change different parameters. I know that in OpenOffice everything is not working as it works in MS Office. The point, why I want to be 100% shure that this is working in OO too,is, if I go completely to OO, I would have a lot of work to do. I cant just copy the sheets into OO from MS Office. Not all calculations can be copied with all the formulas i have.

Thanks a lot again
 
Old 06-29-2006, 01:08 AM   #14
riba43
Member
 
Registered: Feb 2005
Location: Slovenia
Distribution: suse11.0
Posts: 749

Original Poster
Rep: Reputation: 31
no, it definitely is not working. When I open , say 3 docs and I change a value in , say, doc2 it changes a value in doc1, but how can i link doc3 with doc1 at the same time, so when I change a value in doc3 it changes a value in doc1 too?? In reality, I have much mor docs and all of them are linked to the doc1 and links between them are working (in excel of course). And that is one of the reasons why i still have to have WinXp on my comp.
 
Old 06-29-2006, 11:55 AM   #15
abisko00
Senior Member
 
Registered: Mar 2004
Location: Munich
Distribution: Ubuntu
Posts: 3,517

Rep: Reputation: 58
I don't see a reason why it shouldn't work with multiple document as well. Maybe you cannot link to a cell that already refers to another cell. Try to link it to the original cell (the one with the formula).

You may also run into trouble if you create xls sheets and import them into OOorg. I could imagine that not all OOorg functions are available in that format.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
openoffice.org can't use text database in calc or base brickbat Linux - Software 2 07-13-2005 03:43 PM
OpenOffice.org Calc Lookup question BorisMcHack Linux - Software 0 01-31-2005 12:49 PM
OpenOffice.org Calc - Switching Axes in XY graph? apachedude Linux - Software 1 12-05-2004 12:24 PM
OpenOffice.org Calc printing problems Eultza Linux - Newbie 0 09-06-2003 12:33 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Distributions > SUSE / openSUSE

All times are GMT -5. The time now is 07:26 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration