err:508 when referencing Excel Names in another file

Pseudemys · Newbie Joined: 17 May 2007 Posts: 3

I have a series of Excel spreadsheets that contain references to each other, typically maintaining totals and such from month to month. I recently imported them onto my Mac OS X and am trying to use them in OpenOffice.

Where a reference points to a cell in another file, everything is fine.
Example:
=SuperRR+'Macintosh HD:Users:reinhard:Documents:Pindimara Consultants Ltd:[Pindimara200703.xls]pay advice'!$F$15

Where a reference points to a Name of a cell in another file, I get Err:508
Example:
=GrossRR+'Macintosh HD:Users:reinhard:Documents:Pindimara Consultants Ltd:Pindimara200703.xls'!GrossRRYTD

This example was copied using Excel on my Mac. When I look at the same file using Open Office on my Mac, it looks like this:
=GrossRR+#NAME!GrossRRYTD

Is there a way out of this without amending hundreds of references?
Thanks

Villeroy · Posted: Thu May 17, 2007 10:36 am Post subject:

Villeroy · Posted: Thu May 17, 2007 1:01 pm Post subject:

Some more issues:
Calc uses system-independent urls with this kind of links.
='file:///some/path/other.xls'#Sheet1.A1
This creates a hidden data-copy within this file. The hidden sheet is named file:///some/path/other.xls. See Menu:Format>Sheets... The formula actually refers to the cells on the hidden sheet and when you refresh the link (Menu:Edit>Links...) the hidden sheet gets refreshed if possible.
Because your range-name GrossRRYTD does not belong to this document you can not use it.
Try another approach:
=DDE("soffice" ; "some:path:according:to:system:[other.xls]" ; "GrossRRYTD")
This takes 3 strings (protocol, path, bookmark), opens the referred document invisibly and transfers the data.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Pseudemys · Newbie Joined: 17 May 2007 Posts: 3

Thanks for that explanation. I understand what you say about why Calc is complaining about the link.

Since it works under Excel, though, I would have thought that, when Calc opens the XLS file, the link would be converted to something that it understands, rather than left as a bad address.

In my current situation, I simply cut and paste the XLS files from a Windows drive to my iMac drive and then opened them with OO. Is there some other way of importing or converting the file so that Calc correctly interprets the Name function?

I have hundreds, perhaps thousands, of these broken links, and it is not practical to go through all the files and to change them all.

Villeroy · Posted: Fri May 18, 2007 3:13 am Post subject:

Have you ever tried to do the same thing between Win-Excel and Mac-Excel or moving the source files on the same system? I doubt it is possible at all. Make all sheets visible. Have a look at Edit>Links... If there are any links try to change the target files (button "Modify"). Remove all obsolete sheets and hide the remaining 'file:///...' sheets.
Search and replace accross all sheets may help as well.
Another way to create a new link goes like this:
In the target file start a formula with "=" and while in edit-mode point to a cell or range in the opened source file.
Same with DDE-links:
Copy the source range and paste-special with option "Link".
However, your main trouble seems to be that you are using a collection of spreadsheets as a database.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

bobharvey · Posted: Sat May 19, 2007 1:19 am Post subject:

Pseudemys · Newbie Joined: 17 May 2007 Posts: 3

I've been playing around with this over the last week, and it seems to me that the answer is simple: Calc does not support the use of cell "Names" across different files, and Excel does. I have set up a test case of two pure Calc spreadsheets that reference each other using "Name" rather than a cell address, and sure enough they don't work.

So my question becomes: Is Calc likely to support cross-file referencing using Names in the near future, or should I just abandon Calc and buy Excel for the Mac?

FabianFinlay · Newbie Joined: 15 Oct 2007 Posts: 1

Can I just add that I have the same problem as Newbie. I have just installed Open Office and the moment I started using Calc it became apparent that my existing links would not work. I use Names a great deal in Excel so switching to Open Office will be a nono for me unless there is an equivalent function in Calc. javascript:emoticon(' Sad

')
Sad He is not alone.

Very useful forum though as I found this thread on my problem almost immediately.

keme · Posted: Mon Oct 15, 2007 4:02 am Post subject:

I've found the same:

Referencing "remote" ranges (in different files), you must use "A1" type cell references. Referencing named ranges won't work.
Inserting link to external data, you must use range name. Cell references won't work.

A bit inconsistent, I'd say.

huwg · Super User Joined: 14 Feb 2007 Posts: 890

Maybe issue 78030?

keme · Posted: Mon Oct 15, 2007 4:14 am Post subject: