Referencing to a cell name on another sheet

classic Classic list List threaded Threaded
4 messages Options
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Referencing to a cell name on another sheet

Hi!

An example:
Let's say I have two sheets: XSheet and YSheet.
On XSheet I named cell AA1 ”fps”.
In cell XSheet.Z2 I have the following:
=fps*(V2-W2*3600-X2*60-Y2)

This works fine.
However, the same thing doesn't work on YSheet.
The result is #VALUE!
I've also tried things like =XSheet.fps*(V2-W2*3600-X2*60-Y2), but with no
success.
Also, I can't name a cell ”fps” on YSheet, since the name is already taken.

So what is the proper way to handle this? How can I reference a cell name
on another sheet?




Kind regards

Johnny Rosenberg

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Referencing to a cell name on another sheet

Johnny Rosenberg wrote
So what is the proper way to handle this? How can I reference a cell name
on another sheet?
Assuming you are referring to another Sheet in the same file, it does work as you described (using version 5.3.2.2 x64 under Windows 10 x64)
Which OS, LO version are you using?

Here is an example that does work. Named range fps is in cell AA1
Named_range.ods
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Referencing to a cell name on another sheet

In reply to this post by Johnny Rosenberg
At 17:15 02/04/2017 +0200, Johnny Rosenberg wrote:
>An example: Let's say I have two sheets: XSheet and YSheet. On
>XSheet I named cell AA1 "fps". In cell XSheet.Z2 I have the following:
>=fps*(V2-W2*3600-X2*60-Y2)
>This works fine.
>
>However, the same thing doesn't work on YSheet. The result is
>#VALUE! I've also tried things like
>=XSheet.fps*(V2-W2*3600-X2*60-Y2), but with no success. Also, I
>can't name a cell "fps" on YSheet, since the name is already taken.

Is the named rage "fps" defined as "$AA$1"? I think that may be your
problem. What happens if you change the definition explicitly to
"$XSheet.$AA$1" - in other words, to put its allegiance to XSheet in
the definition of the named range instead of trying to put it in the reference?

If you select the required range first and then use Insert | Names >
| Define... to create the name, does the range offered not already
include the sheet name?

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Referencing to a cell name on another sheet

In reply to this post by Johnny Rosenberg
Thanks for your response, Brian, Michael and Pedro.

It seems like I misunderstood what was really happening, and I'm still not
sure.
I opened the Manage names dialogue and the fps name referred to sheet name
and cell, so it should just work. I deleted the name and added it again,
and did a simple test with just =fps on the other sheet and it worked. I'm
not sure why it didn't work before. I probably did something that I wasn't
aware about…

Anyway, your responses inspired me to make it work, I think, thanks again.
Problem solved!


Kind regards

Johnny Rosenberg



2017-04-02 17:58 GMT+02:00 Brian Barker <[hidden email]>:

> At 17:15 02/04/2017 +0200, Johnny Rosenberg wrote:
>
>> An example: Let's say I have two sheets: XSheet and YSheet. On XSheet I
>> named cell AA1 "fps". In cell XSheet.Z2 I have the following:
>> =fps*(V2-W2*3600-X2*60-Y2)
>> This works fine.
>>
>> However, the same thing doesn't work on YSheet. The result is #VALUE!
>> I've also tried things like =XSheet.fps*(V2-W2*3600-X2*60-Y2), but with
>> no success. Also, I can't name a cell "fps" on YSheet, since the name is
>> already taken.
>>
>
> Is the named rage "fps" defined as "$AA$1"? I think that may be your
> problem. What happens if you change the definition explicitly to
> "$XSheet.$AA$1" - in other words, to put its allegiance to XSheet in the
> definition of the named range instead of trying to put it in the reference?
>
> If you select the required range first and then use Insert | Names > |
> Define... to create the name, does the range offered not already include
> the sheet name?
>
> I trust this helps.
>
> Brian Barker
>
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
> ubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted