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] 

INDIRECT_XL

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


Joined: 19 Nov 2007
Posts: 2
PostPosted: Mon Nov 19, 2007 7:42 am    Post subject: INDIRECT_XL Reply with quote
Hello.

First of all I wish to congratulate the OO suite. It's really good work, but I have a small problem.

In the company where I work, ,we have a pc where we had linux (Ubuntu) as OS and others with windows. In all of them we had OO installed (version 2.2 and 2.3).

Sometimes we receive spreadsheats from our costumers with the INDIRECT function. That function is not full compatible with MS Excel and the result will be an error. I have corrected that substituting the "!" for "." and it works fine.

However, in my pc, where I had linux instaled as OS, I realise that in that OO version (2.2 for linux) it translates the indirect function as INDIRECT_XL and no error is given as a result (I can import directly from MS Excel and make no substitutions and no error is given as result).

I have already downloaded the last windows version and INDIRECT_XL is not yet available in that version.

Anyone can help me in finding a windows version with INDIRECT_XL on it?

Thanks
Nuno
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10065
Location: Germany
PostPosted: Mon Nov 19, 2007 9:36 am    Post subject: Reply with quote
This should solve the problem:
Insert>Names>Define... (Ctrl+F3)
Name: shSep
Assigned to: =MID(ADDRESS(1;1;1;"Foo");4;1)
Button "Add", button "OK"
Now =shSep returns sheet separator "." in Calc and "!" in Excel. =INDIRECT("Sheet1"&shSep&"A1") should work in Excel and in Calc.
Back to top
View user's profile Send private message
nf
Newbie
Newbie


Joined: 19 Nov 2007
Posts: 2
PostPosted: Tue Nov 20, 2007 2:44 am    Post subject: Reply with quote
Hello Villeroy.

Thanks for trying help me but I tryed to do that and had no positive results. When I put the expression on a single cell I obtain the expected result ".", but when I do exactly the way you told, I obtain an error as response.

It's more simple if I write here what I have:

In windows version of OO (v 2.3), when I open an excel spreadsheet the formula gives:
=INDIRECT(B$5&"!"&$B$25) and an error as result (Yes, I know that I have to substitute the "!" by "." to work fine, but I want a way to open directly the spreadsheet and don't lose some hours to change all the expressions)

In linux version of OO (v 2.2), when I open the spreadsheet it gives me directly and with no substitution =INDIRECT_XL(B$5&"!"&$B$25) and works fine with no time waist in changing formulas.

I dont know if I made any mistake trying to do what you said in your last post. Can I send the spreadsheet to you to see if you can fix it?

But I already have a question. If the linux version of OO had the INDIRECT_XL function, why the windows version don't have it? I think it would solve the problem on a easy way.

Thanks again
Nuno
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10065
Location: Germany
PostPosted: Tue Nov 20, 2007 6:06 am    Post subject: Reply with quote
nf wrote:
Hello Villeroy.

Thanks for trying help me but I tryed to do that and had no positive results. When I put the expression on a single cell I obtain the expected result ".", but when I do exactly the way you told, I obtain an error as response.

Which error do you obtain in which build (Win/Ubuntu)?
nf wrote:

It's more simple if I write here what I have:

In windows version of OO (v 2.3), when I open an excel spreadsheet the formula gives:
=INDIRECT(B$5&"!"&$B$25) and an error as result (Yes, I know that I have to substitute the "!" by "." to work fine, but I want a way to open directly the spreadsheet and don't lose some hours to change all the expressions)

This is the default behaviour and my workaround is made for Excel files where INDIRECT should work with both programs.

nf wrote:

In linux version of OO (v 2.2), when I open the spreadsheet it gives me directly and with no substitution =INDIRECT_XL(B$5&"!"&$B$25) and works fine with no time waist in changing formulas.

Ubuntu and Novel build their own flavours of OOo including many patches to the source code. Many postings in this forum indicate that there are more bugs than enhancements in those patches, which is the reason why I use Sun's original build with Ubuntu. However, this is the first time I read about INDIRECT_XL and I believe it is designed like this:
If an xls gets loaded, all INDIRECT calls are replaced with INDIRECT_XL without changing the document itself (saving the document with normal INDIRECT). My workaround fails because it passes the "right" separator while the application replaced the entire function with another one expecting the "wrong" separator.
At this point it would be interesting what happens with INDIRECT("Sheet1.A1") when newly entered into an Excel file loaded with OObuntu. Err502 (invalid argument) would indicate that all INDIRECT calls in xls are redirected to INDIRECT_XL.
nf wrote:

I dont know if I made any mistake trying to do what you said in your last post. Can I send the spreadsheet to you to see if you can fix it?

Split up the problem: B1: ="Sheet1"&shSep&"A1" => Sheet1.A1 [in Excel: Sheet1!A1]
=INDIRECT(B1) => value from Sheet1.A1 [assuming there is a 'Sheet1']
This has been used successfully with several versions of OOo, Excel and older versions of OObuntu as well.
nf wrote:

But I already have a question. If the linux version of OO had the INDIRECT_XL function, why the windows version don't have it? I think it would solve the problem on a easy way.

Thanks again
Nuno

The problem is that Ubuntu creates a workaround for Excel compatibility while breaking the compatibility with StarOffice. While Linux developers include questionable patches there seems to be no Windows community at all. Many people ask how to compile OOo under Windows, but we never see any "enhanced versions".

Try this:
shSep IF(ISERROR(INDIRECT("Sheet1.A1"));"!";".") where "Sheet1" needs to be a valid sheet name or replaced with
MID(CELL("filename");FIND("#$";CELL("filename"))+2;LEN(CELL("filename")))
which returns this sheet's name (in OOo at least).

By the way: There is another serious problem with INDIRECT. Does =INDIRECT_XL("NameOfNamedCell") work with OObuntu or does it return Err:502?
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