Referencing to a cell name on another sheet

users
#1

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

#2

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
<http://nabble.documentfoundation.org/file/n4211618/Named_range.ods>

#3

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

#4

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