OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[resolved] Reference named range in another sheet in same do

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
kharisma
General User
General User


Joined: 20 Dec 2010
Posts: 10

PostPosted: Mon Dec 20, 2010 3:04 pm    Post subject: [resolved] Reference named range in another sheet in same do Reply with quote

I want to reference a named cell in anther sheet of the same workbook.

When I enter sheet.$A$1 it works.

When I enter sheet.thing it comes up with #NAME?
Why? the name exists.

I have tried defining the name as
thing => $A$1
or
thing => sheet.$A$1

I don't want it defined as $sheet.$A$1 because it is a range that I want on all sheets


Last edited by kharisma on Tue Dec 21, 2010 1:22 pm; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 21, 2010 6:59 am    Post subject: Reply with quote

All names in Calc are valid for the whole document. There is no separate name space for sheets.
When you open some xls with Sheet1!Name it translates to document wide Sheet1_Name.

In Calc you can define one name that refers to the same range on each sheet. Simply remove the sheet part from the reference.
Name: MyNamedRange
Refers to: $A$1:$X$99
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
kharisma
General User
General User


Joined: 20 Dec 2010
Posts: 10

PostPosted: Tue Dec 21, 2010 12:22 pm    Post subject: Reply with quote

I have a Calc document (that I created from scratch using Calc) which has several pages with the same layout.

For that purpose, using the generic name system works fine.
Thus on each sheet, qty is always column C, price is column E, and so on.

However, each sheet also has a single cell whose label is ActualProfit.
It is this field I want to carry forward to the next sheet, so that on any given sheet, there is a cell named CarriedForward that has the value from the last sheet's ActualProfit.
Apparently the only way to do this is to either give each ActualProfit cell its own specific name, or just refer to the cell by its position on the page, e.g. Page1.$G$1

For now, I have opted to label each of these cells as PageName.ActualProfit, where PageName is the name of the page in question, and the dot is part of the label, imitating the naming conventions of Calc.

Thus, in the list of labels, I now have two or more entries:
ActualProfit => $G$1
Page1.ActualProfit => Page1.$G$1
Page2.ActualProfit => Page2.$G$1

Then on Page2, I can have CarriedForward = Page1.ActualProfit,
and on Page3, it would be CarriedForward = Page2.ActualProfit.

It's just a pain having to specifically name the cell each time, and I was hoping there was some better way of managing. Seems not Sad
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 21, 2010 1:10 pm    Post subject: Reply with quote

Unlike Excel, Calc knows relative sheet names just like relative column letters and relative row numbers.
Having Sheet1, Sheet2, Sheet3 in a workbook,
Sheet1.$A$1 on Sheet2 refers to the previous sheet.
When you copy that reference to Sheet3 it becomes Sheet2.$A$1. When you copy that reference to Sheet1 it becomes #REF! because there is no previious sheet before Sheet1.
$Sheet1.$A$1 on Sheet2 refers to the distinct Sheet1.

So you can copy the relative reference at least.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
kharisma
General User
General User


Joined: 20 Dec 2010
Posts: 10

PostPosted: Tue Dec 21, 2010 1:20 pm    Post subject: Reply with quote

Fair enough

Thanks
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group