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

[kharisma]

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

[Villeroy]

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 http://forum.openoffice.org

[kharisma]

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

[Villeroy]

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 http://forum.openoffice.org

[kharisma]

Fair enough

Thanks