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 Function

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


Joined: 09 Apr 2007
Posts: 3

PostPosted: Tue Apr 10, 2007 6:59 pm    Post subject: INDIRECT Function Reply with quote

I have an indirect function in excel like this:

INDIRECT("'MNA - SATP'!"&ADDRESS(12;4*$B$2+54))

but when i open it in openoffice the result is Err : 502.

What's happened to my function? Does any body want to help me?
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Wed Apr 11, 2007 1:28 am    Post subject: Reply with quote

Just had http://www.oooforum.org/forum/viewtopic.phtml?t=55597 which asked the same Q. The issue is the "!". This is not valid syntax in calc. The delimiter is .

Use the last argument of ADDRESS to specify the sheet name:
In Excel: INDIRECT(ADDRESS(12,4*$B$2+54,,,"'MNA - SATP'"))
gets imported by Calc XLS filter to
In Calc: INDIRECT(ADDRESS(12;4*$B$2+54;;"'MNA - SATP'"))

That way you avoid the ! versus . problem
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 11, 2007 6:33 am    Post subject: Reply with quote

Quote:
In Calc: INDIRECT(ADDRESS(12;4*$B$2+54;;"'MNA - SATP'"))

No missing arguments (two semicolons) in Calc. Use zero instead.
INDIRECT(ADDRESS(12;4*$B$2+54;0;"'MNA - SATP'"))
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Wed Apr 11, 2007 11:16 am    Post subject: Reply with quote

Embarassed Too true
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
glow
Newbie
Newbie


Joined: 11 Apr 2007
Posts: 1

PostPosted: Wed Apr 11, 2007 6:54 pm    Post subject: Another problem in INDIRECT function Reply with quote

Hai....I got problem in INDIRECT function...
I'm a newbie in Open Office especially in Calc....
I try to use INDIRECT function, just as simple function. I read in HELP, INDIRECT function format is INDIRECT(ref), and ref is in text format...so I try to use it and I make this..
INDIRECT (G5) which format is text, but still error (Err:502).

Please anyone explain it to me.... Question Confused

Many thanks:)
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Thu Apr 12, 2007 2:28 am    Post subject: Reply with quote

G5 need not be formatted as text but it must contain a cell reference or a formula which returns a cell reference, e.g A1 or ="A"&"1". Using either of those simple examples, your formula would return the data in A1.
_________________
search forum by month


Last edited by noranthon on Thu Apr 12, 2007 7:41 pm; edited 1 time in total
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Thu Apr 12, 2007 4:17 am    Post subject: Reply with quote

glow, just to expand on what norathon is saying G5 -- that is without surrounding quotes is not a text expression but a cell reference.

"G5" is a text expression. So is "G"&A1 as long as cell A1 contains a number (say 5).

Referring to columns can be a pain because you need to use A-Z,AA-IV notation, but the way to avoid this is to use the ADDRESS function. (Use Help and search the index for ADDRESS Function to get a full description.) Hence if C3-C5 contain a row number, a column number and a Sheet Name (e.g. 4,5,"Sheet2") then =INDIRECT(ADDRESS(C3;C4;0;C50)) will return the value in Sheet2.E4
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
abeere
Newbie
Newbie


Joined: 27 Jan 2009
Posts: 3

PostPosted: Tue Jan 27, 2009 6:22 pm    Post subject: Reply with quote

Hi,

I'm looking to migrate over to OOo, but am having a problem with CALC. Specifically, my code in Excel is:

=INDIRECT(ADDRESS(57+G3;2;;;"Tables"))

Which is returning a set of hashes. I've tried reducing this, and using the information in this thread (as the Help section wasn't of much use), I've reduced this to:

=ADDRESS(1;1;0;"Sheet2")

Which, as I understand it, should return:

Sheet2.A1

However, all I'm getting is
Err:502

If I remove the last two parameters, it works. However, I need to reference values on another sheet. Does anyone have any ideas where I might look for an answer?

P.S. I'm using: OpenOffice.org 3.0.0, specifically: OOO300m9 (Build:9358) and my OS is currently XP.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jan 28, 2009 10:33 am    Post subject: Reply with quote

abeere:

Welcome to the universe of OpenOffice and Calc.

I think you are, almost there. That is, your formula ALMOST correct. And yes, this is something that is 'undocuemtned' per the Calc Help file, in both OpenOffice 3.0 and the new ( as of today 3.0.1 Crying or Very sad ).

There was a 'change' with OpenOffice 3.0 in the arguments of the ADDRESS function, and, apparently, this change has NOT made it to the help file. The change is noted in the link:

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_ADDRESS_function

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_ADDRESS_function wrote:

Additional optional parameter for OpenOffice.org 3:

In ADDRESS, the parameter is _inserted_ as the 4th parameter, shifting the optional sheet name parameter to the 5th position.


Put another way, there is an additional parameter, BEFORE the name of the sheet ( now the fifth argument ).

Also, regarding the third parameter ( Mode ):

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_ADDRESS_function wrote:
mode (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be 1.

Code:
    mode    row      column      example
    1    absolute    absolute    $A$1
    2    absolute    relative    A$1
    3    relative    absolute    $A1
    4    relative    relative    A1


Therefore, if you want the result to be:
abeere wrote:
Which, as I understand it, should return:

Sheet2.A1

The following function should work:
Code:
 =ADDRESS( 1; 1; 4; ; "Sheet2" )

should work ( note above, the optional fourth parameter is not entered BEFORE the sheet name ).

I hope this helps, please be sure to let me / us know.

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jan 28, 2009 1:49 pm    Post subject: Reply with quote

[quote="myself]No missing arguments (two semicolons) in Calc. Use zero instead.
INDIRECT(ADDRESS(12;4*$B$2+54;0;"'MNA - SATP'"))[/quote]

Version 3 supports the same 5 arguments as Excel, but Calc does not support missing arguments. In most cases you can set them to zero, but here it should be:
=INDIRECT(ADDRESS(57+G3;2;1;1;"Tables"))

However, this way of indirect referencing is very cumbersome, incompatible, complex and difficult to debug. It's not easy to concatenate a valid(!) string address with letters and calculated numbers. Just rename the table to make it fail.

Recent ramblings on this issue: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=14780&p=69169#p69169
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
abeere
Newbie
Newbie


Joined: 27 Jan 2009
Posts: 3

PostPosted: Wed Jan 28, 2009 4:31 pm    Post subject: Reply with quote

@ sliderule, thanks for the help, I'm up and running!

I had to do two things:

1) Add back the 5th parameter (which had been there from Excel, but I had removed as per documentation)
2) Change the third parameter to be between 1, 2, 3 or 4. I had it set to NULL or to zero. Neither of which will work. While I understand the different settings, they all work out the same for me as I'm simply dumping the entire output of the ADDRESS function straight into the INDIRECT function.

Therefore, the Excel statement:
=INDIRECT(ADDRESS(57+G3;2;;;"Tables"))

Is now:
=INDIRECT(ADDRESS(57+G3;2;4;;"Tables")) in Calc

@ Villeroy, I'm guessing your statement that there should be no missing arguments in Calc is a guideline/coding standard as opposed to a requirement. The reason I mention this, is that both:
=INDIRECT(ADDRESS(57+G3;2;4;1;"Tables")) and
=INDIRECT(ADDRESS(57+G3;2;4;;"Tables"))
worked for me. Am I correct? Or is it also a matter of creating more portable code?

As for using an alternate to the INDIRECT/ADDRESS pair, I'd welcome one. I'm unlikely to change it for this particular application, but if there is a cleaner way I'm all ears for future apps. However, as long as I'm not manipulating the text string output of the ADDRESS function before it goes through the INDIRECT function, is it really a problem?

And as to the change of a sheet name (i.e. If I changes the sheet "Tables" to "Tables2"), I've always just used a search/replace. Worked like a charm (at least in Excel, and I'm assuming so in Calc).

But as I said, I'm always up to learning a better way of doing things.

Thanks again for the help and insight.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jan 28, 2009 11:01 pm    Post subject: Reply with quote

=INDIRECT(ADDRESS(57+G3;2;4;1;"Tables"))
better known as:
=INDIRECT("Table!B"&57+G3)

References relocated by numbers:
=OFFSET($Tables.$B$1;56+G3;0)
=OFFSET($Tables.$A$1;56+G3;1) [equivalent]
=OFFSET($Tables.$B$1;56+G3;1;1;1) [with optional resizing arguments.]

=INDEX($Tables.$B$1:$B$65536;57+G3)
=INDEX($Tables.$B$1:$B$65536;57+G3;1) [with optional column]
=INDEX($Tables.$B$1:$B$65536;57+G3;1;1) [with optional column and sub-range]
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
abeere
Newbie
Newbie


Joined: 27 Jan 2009
Posts: 3

PostPosted: Thu Jan 29, 2009 7:00 pm    Post subject: Reply with quote

Cool, thank you. I didn't realize there were at least 4 different ways to do what I was doing. The OFFSET command seems a better fit for what I'm trying to do. Much appreciated.

BTW, the
=INDIRECT("Table!B"&57+G3)
didn't quite work me. I needed to replace the "!" with a period. Once I did, it worked perfectly.

Thanks again for the help.
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 30, 2009 1:26 am    Post subject: Reply with quote

Quote:
BTW, the
=INDIRECT("Table!B"&57+G3)
didn't quite work me. I needed to replace the "!" with a period. Once I did, it worked perfectly.


The "!" vs "." is this forum's best known problem in this context.

Second issue is about valid names in one application, but invalid in the other one. Calc may rename tables or names or sometimes you may want to rename. References update their table names like they update moved cells, wherereas a string is just a sequence of characters.

A third problem arises when you try to do the same thing horizontally (what is the column name of column 99, let alone todays 1024 columns?). In Excel you can switch to R1C1- notation INDIRECT("R23C"&999) and Calc's indirect can interprete this now with an additional argument.

Almost anything you can do with INDIRECT can be done with references shifted by mans of numbers.
You need INDIRECT if you let the user pick a range name or range address.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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