Issue 4695

I have just discovered this too. I attach a similar (but different) 
spreadsheet that illustrates it...

Created attachment 1602 [details]
Excel file

Hi Niklas,

one for you I think.

Frank

INDIRECT doesn't support named ranges. We should extend it.

yes, we should

Too bad this function isn't working properly: we can't abandon Excel because in my 
office some fundamental applications needs it :(

Target to OOo 2.0

Raise prio to P3 (only for statistical reasons to indicate that this really
should go into OOo2.0).

according to the announcement on releases
(http://www.openoffice.org/servlets/ReadMsg?list=releases&msgNo=7503) this issue
will be re-targeted to OOo Later.

Sigh.. seems the list of issues to be retargeted was created  before I changed
priority of this one.. so back to OOo2.0.

Sigh again.. product management decided to postpone remaining features from
OOo2.0 to OOoLater. This was part of Q-PCD issue 20494.

Just to be complete on this issue, when extending the indirect() to named cells
it should also be tested for external files, i.e.

=indirect('file://path-to-file.sxc#named_cell')

point being that today this doesn't even work for non-named cells:

=indirect('file://path-to-file.sxc'#sheet1.A1)  doesn't work either

*** Issue 36410 has been marked as a duplicate of this issue. ***

mportier,

External references with non-named cells _do_ work, you just have to pass them
as string to INDIRECT(), as usual:
=indirect("'file://path-to-file.sxc'#sheet1.A1")
instead of
=indirect('file://path-to-file.sxc'#sheet1.A1)
unless you want the content of file://path-to-file.sxc'#sheet1.A1 being
evaluated by INDIRECT() ...

Eike

OpenOffice is a great alternative to MS Excel! Keep up the good work.

The error is already reported, but I wanted to reiterate that this issue is a
critical roadblock to eliminating MS or porting advanced financial applications
to OO. 

Hopefully, it can be addressed soon.

Thanks

*** Issue 55244 has been marked as a duplicate of this issue. ***

*** Issue 58375 has been marked as a duplicate of this issue. ***

Is an solution in nearer future possible?

No. But this is quite on top of the list of features to implement.

Is this going to be fixed soon? if not I can fix & submit

Maremv,

Sure, working patches are welcome. Do you already have a fix in mind?

  Eike

Nope, I have not even looked at the code yet. I just wanted to make sure I was
not duplicating work. I will begin looking into the fix -Thanks

Sorry about the delay on this, I just got an email today that reminded me that I
said I would fixthis...

Ok I looked back at the source code I had downloaded when I originally was
looking at this problem and remembered quickly that I could not really find any
kind of structure to this program. To save me alot of time (because I'm guessing
some of the people on this board know this program pretty well) I need to be
pointed to the following:

Where & What is the source file/function name that has the function INDIRECT in it?
Where & What is the source file/function name that allows you to place =<named
field> into a cell and it will do the look up of that cell name and place the
value of that named cell into the cell?
Where & What is the source file/function name that allows cells values to be
looked up out of external spread sheets so I can make the following work as
requested in one of the messages below: =indirect(
'file://path-to-file.sxc#named_cell' )

Hi Maremv,

Implementation is in sc/source/core/tool/interpr1.cxx method
ScInterpreter::ScIndirect(). After the calls to ConvertDoubleRef()
respectively ConvertSingleRef() the else path containing just
SetIllegalArgument() right now must be extended:

- check whether the string matches a defined name, similar to
  sc/source/core/tool/compiler.cxx ScCompiler::IsNamedRange(), but
  without creating a token, instead

- check whether the range data consists of just one single cell or
  a range, other data like multiple ranges or formula fragments are not
  valid. You may use pRangeData->IsReference() for this.

- Relative reference parts need a special handling, and to be Excel
  compatible the relative offsets have to be calculated with a basis of
  A1 and wrapped around the sheet if negative. I suggest to create
  another ScRangeData::IsReference() method that additionally takes
  a ScAddress position (ScInterpreter::aPos member variable), same for
  ScTokenArray::IsReference(), and enhance
  ScTokenArray::ImplGetReference() to take account of an optional
  position address passed in.

I suggest to refrain from trying to handle the external reference case
at this stage, which would unnecessarily complicate things, and let it
be a separate issue instead. However, if you feel confident that could
be accomplished by digging deep into the lcl_ScAddress_Parse_...()
methods of sc/source/core/tool/address.cxx

Please note that to integrate code or data contributed we need a signed
Joint Copyright Assignment form (JCA) filled-out, see
http://contributing.openoffice.org/programming.html#jca
To be able to lookup your name in the list of approved assignments I'd
appreciate if you stated your full name here in this issue.

  Eike

I Have already filled out one of thoes forms and emailed it to
copyrightfax@sun.com. My full name is William Neil Hall. Thanks for the point in
the right direction, now let me dig in a little. -Thanks

Hi William,

Yes, confirming your name is listed, so we're fine to proceed once we receive
your patch. Please go ahead :-)

Btw, forget the ScInterpreter::aPos member variable I mentioned above, that's
nonsense since we do not want the current formula's position but A1 instead, sorry.

Thanks
  Eike

I have been looking for a resource page on openoffice.org on the best way to
compile my OO changes, but not having much luck. Can someone point me to a web
page? or tell me the best set of tools to use for Win-XP? -Thanks

Please see
http://wiki.services.openoffice.org/wiki/Building_OpenOffice.org
http://wiki.services.openoffice.org/wiki/Windows
http://tools.openoffice.org/dev_docs/build_windows_tcsh.html

*** Issue 73962 has been marked as a duplicate of this issue. ***

Is an solution in nearer future possible?
Thanks.

William (maremv) volunteered to work on this, don't know about progress.

William, how are things proceeding?

  Eike

Thanks er,
this Issue is very important for our company.
We want that all users use Open Office, but without this function it is not
possible.
Our customer calculation depends on it.

Sorry I just haven't had enough time to work on this. It might be best if
someone else tries to fix.

Is there any chance of this issue being dealt with in 2.3?  It's been around for
5 years without resolution.

@kingshome: if you find someone who'll implement it. I for myself won't have
time to do it for 2.3.

Created attachment 44510 [details]
A patch which tries yo fix it

Hello there,

   Here is a patch which fix this issue. Hope this helps. 


Regards,

changed Issue Type to PATCH

adding me to cc - sophie

Hi Michel,

Thanks for contributing. The patch generally takes the right approach,
but doesn't handle relative references. I'll restate the requirement
from #desc25 here:

- Relative reference parts need a special handling, and to be Excel
  compatible the relative offsets have to be calculated with a basis of
  A1 and wrapped around the sheet if negative. I suggest to create
  another ScRangeData::IsReference() method that additionally takes
  a ScAddress position (ScInterpreter::aPos member variable), same for
  ScTokenArray::IsReference(), and enhance
  ScTokenArray::ImplGetReference() to take account of an optional
  position address passed in.

To accomplish that, the easiest and safest would be to clone the
ScTokenArray (pCode) in the newly to be implemented
ScRangeData::IsReference() method, stuff it into a compiler instance and
call MoveRelWrap(), similar to what is done in
ScRangeData::UpdateSymbol(), and then call the newly to be created
ScTokenArray::IsReference().

Btw, interpreter functions should never simply return without having set
an error value and/or pushed a result. The reason I mention this is the
line
+     if (rData == NULL) return;
of your patch. Btw2, in our naming conventions rData should be named
pData instead, since it is a pointer. If a premature return is needed,
the code instead should be something like
        if (pData == NULL)
        {
            SetError(errUnknownState);
            PushInt(0);
            return;
        }

Please note that to integrate code or data contributed we need a signed
Joint Copyright Assignment form (JCA) filled-out, see
http://contributing.openoffice.org/programming.html#jca

Thanks
  Eike

Thanks for your answer Eike. Here is mine :

>- Relative reference parts need a special handling, and to be Excel
>  compatible [...]
Can you please explain to me what exactly is a relative reference ? My patch
works well on samples documents. 

>Btw, interpreter functions should never simply return without having set
>an error value and/or pushed a result. The reason I mention this is the
>line
>+     if (rData == NULL) return;
Ok. I didn't really know what error should be returned. It was more for escaping
segv than anything else. It's not supposed to be an error in first place. But
you're right. 

>of your patch. Btw2, in our naming conventions rData should be named
>pData instead, since it is a pointer. If a premature return is needed,
>the code instead should be something like
        if (pData == NULL)
        {
            SetError(errUnknownState);
            PushInt(0);
            return;
        }
Heck, 3 lines for return, you're hard with me, I don't like code too long :). 

I will do the change to pData and for premature return in a patch. My CWS is not
here, but it's coming nicely and surely, thanks Sophie ^_^.

Regards,
Michel

Hi Michel,

Relative references are displayed without the dollar character, in the
names dialog you may define them by editing the reference and removing
the dollars, for example modify $Sheet1.$A$1 to $Sheet.A1

The relative reference generated then depends on the position of the
cell cursor when you opened the name dialog, for example if the current
cell is B2, a reference of A1 actually means "one row up and one column
to the left". If you reopen the dialog on a different cell the reference
of that named range displayed is adjusted accordingly.

Note that the requirement I copied above actually has an error stating
"(ScInterpreter::aPos member variable)", for Excel compatibility this is
exactly not the case, but named relative references for the INDIRECT
function are based on A1 instead. Weird, and IMHO wrong, but that's how
Excel does it. Excel doesn't know relative sheet references in named
ranges, but Calc does, so the address to pass from within
ScInterpreter::ScIndirect() probably should be
ScAddress( aPos.Tab(), 0, 0)

> Heck, 3 lines for return, you're hard with me, I don't like code too
> long :). 

Well, the more common errors have convenience functions, such as
SetIllegalArgument() that sets the error and pushes a value, so one line
less to code, an errUnknownState just isn't that usual..

> I will do the change to pData and for premature return in a patch. My
> CWS is not here, but it's coming nicely and surely, thanks Sophie ^_^.

Huh? Does hat mean you want to create your own CWS for this? Do you
already have CVS write access, which is needed to do so?

  Eike

Although XL seems to limit the content of the names to be simple, I don't see
why we want to continue that.  In which case the same problem that arose with
the validation lists comes up, how to interpret a general tokenarray in a
context outside a cell.

eg

define FOO as IF(A1;A2;A3)
=INDIRECT("FOO")

XL compat will also require support for parsing
    Sheet!name

-> jodygoldberg : Do you have any clues on how to do that easily ? So I can
update my patch ?

Regards,

*** Issue 79354 has been marked as a duplicate of this issue. ***

Created attachment 49656 [details]
patch built upon Michel's earlier patch (SRC680_m236)

Hi *,

I had to work on this issue per customer request.  I basically applied Michel's
patch, cleaned up a bit, and applied the same principle to INDIRECT_XL as well
(INDIRECT_XL is disabled upstream at the moment).

Jody mentioned to cover named expressions, but IMO that should be a separate
issue to tackle as this patch alone solves many users' problems.

Kohei

I'm still working on handling the relative reference.  So, the patch I just
posted only works reliably.  Expect an additional patch soon.

Fine patch, thanks. As discussed on IRC, the case when the defined name contains
relative references needs to be handled though, else users will rely on a result
of foo:=B3 being identical to foo:=$B$3.

bah!  I meant to say "the patch I just posted only works reliably _on named
ranges containing absolute references_."

->kohei: It's a nice and pretty way to have one line for exit. I really like
your improvement of your patch. Thanks a lot for showing me this cool approach.

Created attachment 49659 [details]
revised patch to handle relative references (SRC680_m236)

I think I got it.  All I did was follow Eike's fine instructions.  It was pretty
much painless. ;-)

mloiseleur: yup, that is a well-known (and very useful) construct when you need
to emulate a try-catch block where you can't really use exceptions.  It also
helps reduce indentations which helps readability.

Created attachment 49660 [details]
fixed memory leak - delete temporary ScTokenArray instance when going out of scope.

@kohei,er: so the target should be moved from OOoLater to OOo 2.4 ?

Much appreciated, Kohei, thank you!

Kohei, please take over and add your patch to a CWS for OOo2.4
Thanks
  Eike

fixed in SRC680/kohei01 cws.

reassigning to fst for verification

found fixed on cws kohei01

found integrated on master OOHm5 using Linux, Solaris and Windows builds

Does the 'fixed' label on this issue refer to ooo 2.4 or the upcoming ooo 3?
The reason I ask is because a problem I found in an xls file still hasn't been
resolved in ooo 2.4.  I thought it was caused by the problem with INDIRECT, but
if this issue has now been resolved, I'll have to look for other reasons.

@kingshome: this patch has been integrated into 2.4, so whatever problem you
have is a new problem.  Please file a separate issue (or try to find an existing
issue if exists) describing the problem you're experiencing.  Thanks!

I tried to use indirect inside vlookup / hlookup and it doesn't work

A1 cell contains the name of a named range, but formulas like :

vlookup ("A";indirect("A1");3;0)

doesn't work

I suppose, it should work if this issue is closed 

I'm testing it with openoffice 3.2.1. I will test in 3.3 again

Thanks!