[Solved] INDIRECT differences from Excel

[Ken Burnside]

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

[TerryE]

Yes you are correct.

See http://qa.openoffice.org/issues/show_bug.cgi?id=4695. Due to be fixed in 2.4

[gjhicks]

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,

[TerryE]

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.

[acknak]

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) } ?

[acknak]

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.

[gjhicks]

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.