[Solved] INDIRECT differences from Excel

Discuss the spreadsheet application

[Solved] INDIRECT differences from Excel

Postby Ken Burnside » Tue Dec 18, 2007 8:53 pm

I'm using Windows XP Pro, SP2. I'm using Excel 2000 and I'm using OO.o Calc 2.3.1

I use the following formula a great deal:

=IF(INDEX(INDIRECT(K$2);$AM3;1)="";"";INDEX(INDIRECT(K$2);$AM3;1))

In Excel, it looks like this:

=IF(INDEX(INDIRECT(K$2),$AM3,1)="","",INDEX(INDIRECT(K$2),$AM3,1))

Cell K2 has the name of the table I'm indexing from. Column AM has the row of the table I'm indexing from.

I have a form where the users can choose which of 10 tables to display; the form has room for only 6, but only 6 (and not the same six) are needed at any one time.

Now, in OO.o Calc, 2.3.1, that first formula returns Err: 502. In Excel, everything works fine.

I've read that the following formula:

=INDIRECT(Name1)

does not work in OO.o - that INDIRECT cannot be used explicitly with a named range.

Why is it not pulling the name of the named range used for the INDEX function here?

I have a much older version of the spreadsheet that uses nested IF statements to pull the named ranges and choose which INDEX function to run; I had to change that when we went from 7 available tables to 10. I recently opened it in OO.o, and it does work - but I'm hitting hard at the limit of nested IF statements at it and can't put the "blank cell capture" to avoid having #N/A show up when a user leaves a table slot empty.

I'm stumped!

- Ken Burnside
Last edited by Hagar Delest on Sun Mar 20, 2011 12:42 am, edited 3 times in total.
Reason: tagged [Solved].
Ken Burnside
 
Posts: 1
Joined: Tue Dec 18, 2007 8:01 pm

Re: INDIRECT differences from Excel

Postby TerryE » Sun Dec 23, 2007 4:09 pm

Yes you are correct.

See http://qa.openoffice.org/issues/show_bug.cgi?id=4695. Due to be fixed in 2.4
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: [Issue] INDIRECT differences from Excel

Postby gjhicks » Tue Jan 22, 2008 11:26 am

Hi,

As I use the indirect() to reference named ranges extensively, I have been waiting for this "bug" to be fixed.

Had a look at the Issue 4695 page, where it notes that this bug has been "fixed", does this confirm that it will be included in the 2.4 release??

Look forward to a reply,
gjhicks
 
Posts: 2
Joined: Tue Jan 22, 2008 10:27 am

Re: [Issue] INDIRECT differences from Excel

Postby TerryE » Tue Jan 22, 2008 2:04 pm

When the target is given this is nearly always scheduled for inclusion in the specified release. So the ans is a very probably, but not quite a definite.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: [Issue] INDIRECT differences from Excel

Postby acknak » Tue Jan 22, 2008 4:02 pm

It gets a bit difficult at this stage of an issue's lifetime, but you can follow the status through the "CWS" ID that's given in the comments, by using the "EIS" link at the left of the Issuezilla page.

The fix is finished ("fixed"), and it works ("verified") but it has not yet been merged into the release branch ("integrated"). That is due to happen any day now (the estimated due date was last Friday), so I'll be surprised if it does not appear in the next developer snapshot (or possibly the one after that).

If this is something you use a lot, you may want to try one of the snapshots and report any problems you find.

BTW, it looks like you're copying each cell of the tables individually. Is there some reason you can't copy the whole table with something like { =INDIRECT(TableName) } ?
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Issue] INDIRECT differences from Excel

Postby acknak » Wed Jan 23, 2008 9:39 pm

I'll be surprised if it does not appear in the next developer snapshot

Woohoo! I do enjoy not being unpleasantly surprised once in a while ;-)

The enhancement is in the "OOH_m4" snapshot that was released today.

PS:
I can define names for two tables TBLX & TBLY, then write { =INDIRECT(A1) } and see the contents of either table, depending on the name I enter in A1.

However, the array formula doesn't work for tables with different geometries (#rows or #columns), so that's a bit of a limitation.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Issue] INDIRECT differences from Excel

Postby gjhicks » Sun Jan 27, 2008 12:31 pm

Yes, the INDIRECT function will 'understand' named ranges in the "OOH_m4" snapshot version.

Am very pleased, as this <should> allow me to fully migrate to OpenOffice.

But, a small problem.

The worksheets in which I use the INDIRECT function also make use of a macro/function to import Yahoo stock quote data. This post in the other OO forum: http://www.oooforum.org/forum/viewtopic.phtml?t=16741 gives details of the macro/function.

This macro/function imports the data into an array. When I tried to record a macro (using the Tools menu facility) the recorded macro did not 'remember' the 'control-shift-enter' to create the array.

I realise that this is off-topic but can anyone advise if it is possible to create an array with the 'record macro' facility??

Thanks,

Geoff.
gjhicks
 
Posts: 2
Joined: Tue Jan 22, 2008 10:27 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests