INDIRECT & ADDRESS in 2.0 problems

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

INDIRECT & ADDRESS in 2.0 problems

dave s-4
Hi all,

A few months ago I wrote a spreadsheet, worked great on an older version of
OpenOffice using indirect operators. Now I have upgraded to 2.0 and my
spreadsheet has broke.

BL8 contains ...
Sheet1_2

So I had tons of equations like the following ...
=INDIRECT(ADDRESS(118;1;1;INDIRECT("BL8")))
Used to returned the call value of 118,1 on Sheet1_2

Now I get #VALUE! everywhere

=ADDRESS(118;1;1;INDIRECT("BL8"))
& I get #VALUE!

=ADDRESS(118;1;1;'Sheet1_2')
& I get R118C1

Where as I would expect something like
=Sheet1_2.A118
which, by the way, returns the correct value

Any ideas anyone, I have spent some hours on this and am stumped :(

Cheers

Dave






---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: INDIRECT & ADDRESS in 2.0 problems

David Chapman
The indirect, address functions working good for me - also on 2.0

Here is my test sheet...

http://www.openofficetips.com/downloads/indirect.ods

I can't see what is wrong with your attached examples - I tried to break OOo
but failed.

Feel free to email me your prob sheet .

Dave

On 11/20/05, dave s <[hidden email]> wrote:

>
> Hi all,
>
> A few months ago I wrote a spreadsheet, worked great on an older version
> of
> OpenOffice using indirect operators. Now I have upgraded to 2.0 and my
> spreadsheet has broke.
>
> BL8 contains ...
> Sheet1_2
>
> So I had tons of equations like the following ...
> =INDIRECT(ADDRESS(118;1;1;INDIRECT("BL8")))
> Used to returned the call value of 118,1 on Sheet1_2
>
> Now I get #VALUE! everywhere
>
> =ADDRESS(118;1;1;INDIRECT("BL8"))
> & I get #VALUE!
>
> =ADDRESS(118;1;1;'Sheet1_2')
> & I get R118C1
>
> Where as I would expect something like
> =Sheet1_2.A118
> which, by the way, returns the correct value
>
> Any ideas anyone, I have spent some hours on this and am stumped :(
>
> Cheers
>
> Dave
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--
My OpenOffice Calc Website
http://www.openofficetips.com
Reply | Threaded
Open this post in threaded view
|

Re: INDIRECT & ADDRESS in 2.0 problems

dave s-4
On Sunday 20 November 2005 10:49, David Chapman wrote:

> The indirect, address functions working good for me - also on 2.0
>
> Here is my test sheet...
>
> http://www.openofficetips.com/downloads/indirect.ods
>
> I can't see what is wrong with your attached examples - I tried to break
> OOo but failed.
>
> Feel free to email me your prob sheet .
>
> Dave
>

Thanks for your reply,

I opened your test sheet and got ...

  Foo
  Foo  =INDIRECT("C2")
  Err:502  =ADDRESS( 2;3;1;"Foo")
  42  =INDIRECT("Foo.C2")
  Err:502  =INDIRECT(B4)
  Err:502  =INDIRECT(ADDRESS(2;3;1;"Foo"))
  #VALUE!  =INDIRECT(ADDRESS(2;3;1;INDIRECT("C2")))

Looks like it broke on my openoffice 2.0 or am I doing something dumb ?
(Please do not rule out dumb, I am really good at it ;))

Dave

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: INDIRECT & ADDRESS in 2.0 problems

dave s-4
In reply to this post by David Chapman
On Sunday 20 November 2005 10:49, David Chapman wrote:

> The indirect, address functions working good for me - also on 2.0
>
> Here is my test sheet...
>
> http://www.openofficetips.com/downloads/indirect.ods
>
> I can't see what is wrong with your attached examples - I tried to break
> OOo but failed.
>
> Feel free to email me your prob sheet .
>
> Dave
>
The crux of the problem appears to be ...

THE HELP PAGE ......................................................

Syntax
ADDRESS(row; column; abs;sheet)
row represents the row number for the cell reference
column represents the column number for the cell reference (the number, not
the letter)
abs determines the type of reference:
1: absolute ($A$1)
2: row reference type is absolute; column reference is relative (A$1)
3: row (relative); column (absolute) ($A1)
4: relative (A1)
sheet represents the name of the sheet. It must be placed in double quotes.
Example:
ADDRESS(1; 1; 2; "Sheet2") returns the following: Sheet2.A$1
If the cell A1 in sheet 2 contains the value -6, you can refer indirectly to
the referenced cell using a function in B2 by entering =ABS(INDIRECT(B2)).
The result is the absolute value of the cell reference specified in B2, which
in this case is 6.

MY RESULT ................................................................

=ADDRESS(1; 1; 2; "Sheet2")
gives a result of ..
Err:502

& yep there is a 'Sheet2'

Dave


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: INDIRECT & ADDRESS in 2.0 problems

David Chapman
 In case no one believes me :)

B C
  1




 2
Foo


 3 Foo =INDIRECT("C2")


 4 Foo.$C$2 =ADDRESS( 2;3;1;"Foo")


 5 42 =INDIRECT("Foo.C2")


 6 42 =INDIRECT(B4)


 7 42 =INDIRECT(ADDRESS(2;3;1;"Foo"))


 8 42 =INDIRECT(ADDRESS(2;3;1;INDIRECT("C2")))



On 11/20/05, dave s <[hidden email]> wrote:

>
> On Sunday 20 November 2005 10:49, David Chapman wrote:
> > The indirect, address functions working good for me - also on 2.0
> >
> > Here is my test sheet...
> >
> > http://www.openofficetips.com/downloads/indirect.ods
> >
> > I can't see what is wrong with your attached examples - I tried to break
> > OOo but failed.
> >
> > Feel free to email me your prob sheet .
> >
> > Dave
> >
> The crux of the problem appears to be ...
>
> THE HELP PAGE ......................................................
>
> Syntax
> ADDRESS(row; column; abs;sheet)
> row represents the row number for the cell reference
> column represents the column number for the cell reference (the number,
> not
> the letter)
> abs determines the type of reference:
> 1: absolute ($A$1)
> 2: row reference type is absolute; column reference is relative (A$1)
> 3: row (relative); column (absolute) ($A1)
> 4: relative (A1)
> sheet represents the name of the sheet. It must be placed in double
> quotes.
> Example:
> ADDRESS(1; 1; 2; "Sheet2") returns the following: Sheet2.A$1
> If the cell A1 in sheet 2 contains the value -6, you can refer indirectly
> to
> the referenced cell using a function in B2 by entering =ABS(INDIRECT(B2)).
> The result is the absolute value of the cell reference specified in B2,
> which
> in this case is 6.
>
> MY RESULT ................................................................
>
> =ADDRESS(1; 1; 2; "Sheet2")
> gives a result of ..
> Err:502
>
> & yep there is a 'Sheet2'
>
> Dave
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--
My OpenOffice Calc Website
http://www.openofficetips.com
Reply | Threaded
Open this post in threaded view
|

Re: INDIRECT & ADDRESS in 2.0 problems

dave s-4
On Sunday 20 November 2005 15:03, David Chapman wrote:

>  In case no one believes me :)
>
> B C
>   1
>
>
>
>
>  2
> Foo
>
>
>  3 Foo =INDIRECT("C2")
>
>
>  4 Foo.$C$2 =ADDRESS( 2;3;1;"Foo")

Ahh if only I got a  Foo.$C$2 as output !

>
>
>  5 42 =INDIRECT("Foo.C2")
>
>
>  6 42 =INDIRECT(B4)
>
>
>  7 42 =INDIRECT(ADDRESS(2;3;1;"Foo"))
>
>
>  8 42 =INDIRECT(ADDRESS(2;3;1;INDIRECT("C2")))
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [SOLVED] INDIRECT & ADDRESS in 2.0 problems

dave s-4
In reply to this post by David Chapman
Its not in the OpenOffice help pages, Its not Googlable BUT it is in the
function wizard :) !!

ADDRESS(row;col;ABS;A1;sheet)

Yep looks like a new option for ADDRESS, added it & all is well.

Thanks for all your help

Dave

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [SOLVED] INDIRECT & ADDRESS in 2.0 problems

Niklas Nebel
dave s wrote:
> Its not in the OpenOffice help pages, Its not Googlable BUT it is in the
> function wizard :) !!
>
> ADDRESS(row;col;ABS;A1;sheet)
>
> Yep looks like a new option for ADDRESS, added it & all is well.

Not all that well. This new parameter is not in the "official" version
from the OOo website, for the very reason that it would break existing
documents.

I suppose you're using a version that came with your Linux distribution.
Some of them chose to ignore compatibility, and by using the new
parameter, you're tying yourself to that version.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [SOLVED] INDIRECT & ADDRESS in 2.0 problems

dave s-4
On Monday 21 November 2005 10:13, Niklas Nebel wrote:

> dave s wrote:
> > Its not in the OpenOffice help pages, Its not Googlable BUT it is in the
> > function wizard :) !!
> >
> > ADDRESS(row;col;ABS;A1;sheet)
> >
> > Yep looks like a new option for ADDRESS, added it & all is well.
>
> Not all that well. This new parameter is not in the "official" version
> from the OOo website, for the very reason that it would break existing
> documents.
>
> I suppose you're using a version that came with your Linux distribution.
> Some of them chose to ignore compatibility, and by using the new
> parameter, you're tying yourself to that version.

Ahh I see the problem, I am using kubuntu, stock install. So it may well break
again if I try & use the spreadsheet on an OO in XP ?

Dave




>
> Niklas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [SOLVED] INDIRECT & ADDRESS in 2.0 problems

CPHennessy
On Mon November 21 2005 19:09, dave s wrote:

> On Monday 21 November 2005 10:13, Niklas Nebel wrote:
> > dave s wrote:
> > > Its not in the OpenOffice help pages, Its not Googlable BUT it is in
> > > the function wizard :) !!
> > >
> > > ADDRESS(row;col;ABS;A1;sheet)
> > >
> > > Yep looks like a new option for ADDRESS, added it & all is well.
> >
> > Not all that well. This new parameter is not in the "official" version
> > from the OOo website, for the very reason that it would break existing
> > documents.
> >
> > I suppose you're using a version that came with your Linux distribution.
> > Some of them chose to ignore compatibility, and by using the new
> > parameter, you're tying yourself to that version.
>
> Ahh I see the problem, I am using kubuntu, stock install. So it may well
> break again if I try & use the spreadsheet on an OO in XP ?

Thats exactly what Niklas was saying.

--
CPH : openoffice.org contributor

Maybe your question has been answered already?
                                http://user-faq.openoffice.org/#FAQ

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]