Calculations with data from external links

wedesoi · General User Joined: 06 Jul 2006 Posts: 6

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

jrkrideau

wedesoi · General User Joined: 06 Jul 2006 Posts: 6

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

Villeroy · Posted: Thu Jan 18, 2007 3:58 pm Post subject:

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 http://forum.openoffice.org

wedesoi · General User Joined: 06 Jul 2006 Posts: 6

='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

wedesoi · General User Joined: 06 Jul 2006 Posts: 6

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

jrkrideau · Posted: Fri Jan 19, 2007 4:45 am Post subject:

Villeroy · Posted: Fri Jan 19, 2007 6:52 am Post subject:

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 http://forum.openoffice.org

wedesoi · General User Joined: 06 Jul 2006 Posts: 6

It was the formating in the referenced sheets. Once I switched to number format it works fine. Thanks loads to everyones input.
_________________
DeSoi