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

Calculations with data from external links

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


Joined: 06 Jul 2006
Posts: 6

PostPosted: Mon Jan 08, 2007 6:06 pm    Post subject: Calculations with data from external links Reply with quote

I am trying to do calculations in a worksheet with values that come from external links to sheets in other workbooks, but it seems that only a few of the links are usable, even though they all appear in the worksheet. There is an apparent difference in the formatting of the cells that work compared with the ones that don't, even though they all have the same cell format. The links that work properly show the correct formatting.

Can anyone explain this.
_________________
DeSoi
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Tue Jan 09, 2007 6:36 am    Post subject: Re: Calculations with data from external links Reply with quote

wedesoi wrote:
I am trying to do calculations in a worksheet with values that come from external links to sheets in other workbooks, but it seems that only a few of the links are usable, even though they all appear in the worksheet. There is an apparent difference in the formatting of the cells that work compared with the ones that don't, even though they all have the same cell format. The links that work properly show the correct formatting.

Can anyone explain this.


What OS and version of OOo are you using?

What are the apparent differences ?

Can you supply examples?
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
wedesoi
General User
General User


Joined: 06 Jul 2006
Posts: 6

PostPosted: Thu Jan 18, 2007 2:48 pm    Post subject: Reply with quote

I am using windows xp and OOo 2.1

The functional links are formatted as decimal numbers the non functional links are formatted as integers

I could supply examples but I am not sure how to attach the files.
_________________
DeSoi
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Jan 18, 2007 3:58 pm    Post subject: Reply with quote

How did you create the links? Can you provide examples of working and failing formulas?
There are at least 5 methods to link data in Calc:
='file:///C:/path/document.ods'#$Sheet1.A1
=DDE("soffice";"C:\path\document.ods";"$Sheet1.A1")
Insert>Sheet From File... with option "Link"
Insert>Link To External Data...
and from data-sources, which may be databases, text files and spreadsheets as well.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
wedesoi
General User
General User


Joined: 06 Jul 2006
Posts: 6

PostPosted: Thu Jan 18, 2007 4:27 pm    Post subject: Reply with quote

='file:///C:/Documents and Settings/Bill/My Documents/Leavitt/Curriculum/Physics/RFAS/New Folder/OpenDoc RFAS/Group 03.ods'#$'01'.U5

This is an example of a link. They all look pretty much like this one but some work and others don't.

They were all created the same way by opening the worksheet and clicking on the cell
_________________
DeSoi
Back to top
View user's profile Send private message
wedesoi
General User
General User


Joined: 06 Jul 2006
Posts: 6

PostPosted: Thu Jan 18, 2007 4:44 pm    Post subject: Reply with quote

I should clarify.

The data seems to transfer but if I attempt to do any caculation with most of the cells act as though they are empty
_________________
DeSoi
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Fri Jan 19, 2007 4:45 am    Post subject: Reply with quote

wedesoi wrote:
I should clarify.

The data seems to transfer but if I attempt to do any caculation with most of the cells act as though they are empty


Place to post files mytempdir.com http://www.mytempdir.com

Is there any chance that the other workbooks have the data formatted as text? This is a percistent problem especially with data originally imported from Excel.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jan 19, 2007 6:52 am    Post subject: Reply with quote

The formula you mentioned actually refers to cell U2 in a hidden sheet named 'file:///C:/Documents%20and%20Settings/Bill/My%20Documents/Leavitt/Curriculum/Physics/RFAS/New%20Folder/OpenDoc%20RFAS/Group%2003.ods'#$01
It is a data-copy of sheet 01 in the source file. You can see the hidden copy when you call Format>Sheets>Show... When sheet 01 in the source file has changed, you need to refresh this copy manually by calling Edit>Links... in order to update the data-copy.
When the sheet-name "01" has been changed in the source-file, you need to edit the formulas in the target-file and remove the invalid link.
This kind of link is particullary useful if you want to keep data-copies in the target file in order to get a portable file. The links keep working even if the source is temporarily unavailable. But it may cause trouble when the source-file and it's sheets are moving. Other problems are caused by quotes in the path of the source-file like in ='file:///C:/Dan'sFolder/source.ods'#$Sheet1.A1
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
wedesoi
General User
General User


Joined: 06 Jul 2006
Posts: 6

PostPosted: Thu Jan 25, 2007 5:00 pm    Post subject: Reply with quote

It was the formating in the referenced sheets. Once I switched to number format it works fine. Thanks loads to everyones input.
_________________
DeSoi
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