INDIRECT Function

ria · Newbie Joined: 09 Apr 2007 Posts: 3

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?

TerryE · Super User Joined: 16 Jul 2006 Posts: 550 Location: UK

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.

Villeroy · Posted: Wed Apr 11, 2007 6:33 am Post subject:

TerryE · Super User Joined: 16 Jul 2006 Posts: 550 Location: UK

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.

glow · Newbie Joined: 11 Apr 2007 Posts: 1

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

Many thanks:)

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

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

TerryE · Super User Joined: 16 Jul 2006 Posts: 550 Location: UK

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.

abeere · Newbie Joined: 27 Jan 2009 Posts: 3

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.

Sliderule · Posted: Wed Jan 28, 2009 10:33 am Post subject:

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

Villeroy · Posted: Wed Jan 28, 2009 1:49 pm Post subject:

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

abeere · Newbie Joined: 27 Jan 2009 Posts: 3

@ 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.

Villeroy · Posted: Wed Jan 28, 2009 11:01 pm Post subject:

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

abeere · Newbie Joined: 27 Jan 2009 Posts: 3

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.

Villeroy · Posted: Fri Jan 30, 2009 1:26 am Post subject: