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

err:508 when referencing Excel Names in another file

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


Joined: 17 May 2007
Posts: 3

PostPosted: Thu May 17, 2007 12:14 am    Post subject: err:508 when referencing Excel Names in another file Reply with quote

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
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 May 17, 2007 10:36 am    Post subject: Reply with quote

Help wrote:

508
Error: Pair missing
Missing bracket, for example, closing brackets, but no opening brackets

OK, that's misleading, but "!" is Excel's sheet-separator. Calc uses "." like in SheetName.A1:B5
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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 May 17, 2007 1:01 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Pseudemys
Newbie
Newbie


Joined: 17 May 2007
Posts: 3

PostPosted: Thu May 17, 2007 5:34 pm    Post subject: Reply with quote

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.
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 May 18, 2007 3:13 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
bobharvey
Super User
Super User


Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

PostPosted: Sat May 19, 2007 1:19 am    Post subject: Reply with quote

Villeroy wrote:
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.
I think that is true. Microsoft can make any number of things work on microsoft-only systems. As soon as you mix operating systems or applications from multiple vendors, you fall back on published standards and oddities can arise because certain operations become impossible
Back to top
View user's profile Send private message
Pseudemys
Newbie
Newbie


Joined: 17 May 2007
Posts: 3

PostPosted: Wed May 23, 2007 3:48 am    Post subject: Getting closer.... will Calc ever support cell Names? Reply with quote

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?
Back to top
View user's profile Send private message
FabianFinlay
Newbie
Newbie


Joined: 15 Oct 2007
Posts: 1

PostPosted: Mon Oct 15, 2007 3:44 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Oct 15, 2007 4:02 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Mon Oct 15, 2007 4:03 am    Post subject: Reply with quote

Maybe issue 78030?
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Oct 15, 2007 4:14 am    Post subject: Reply with quote

huwg wrote:
Maybe issue 78030?
Thanks, you saved me the trouble of searching. Voted for that one.
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