Referencing Range names in formulas from different tabs

[merrillb39]

In a workbook I use to calculate income taxes, I have sheets for individual forms, defined range names for cells, and formulas that reference range names in various sheets. Last year, in an earlier version of AOO, referencing range names from different sheets worked. This year, in V4.1.1, the formulas do not work.

Example: In a sheet named "Schedule_D", I have defined the name "Sched_D_Line16" for Cell f24. There is a value of -6724 in that cell. If I try to reference that cell from another Sheet, (=Sched_D_Line16) the value shows as 0. I can place the same formula on any blank area of the sheet named "Schedule_D", and the value shows correctly. I can get the value by using the absolute cell reference (=Schedule_D.f24), but the ability to use the named range seems so much easier.

Is there something that prevents me from referencing named ranges on another sheet or is there some setting that will allow me to do so?

[RusselB]

I found that complicated (for lack of a better term) range names are more prone to errors than simpler names. Don't forget that range names are not sheet specific, thus if you have a range name of Line_16 on a sheet called Sched_D it will be different from range Line_16 on sheet Sched_ZZ
There may be a limit as to the number of characters allowed for a range name, so try using a shorter name,

[acknak]

I have found some odd behavior with range names in older versions of Calc, and there was a pretty nasty bug in a recent version where the names were not being processed properly when the sheet was first opened (as I remember). Anyway, I wouldn't be surprised if there are still some problems lurking there.

You should be aware that the cell references for range names can be relative or absolute just like regular cell references. That is, if the current cell is E24 and you define Sched_D_Line16 as F24, then the name will refer to "the cell to the right of the current cell on the current sheet". If the name should refer to a specific cell on a specific sheet, it has to be defined as an absolute reference: Sched_D_Line16=$Schedule_D.$F$24

The sheet name can be relative as well, so a name like Schedule_D.$F$24 (no $ for the sheet name) will reference different sheets depending on where the formula is located.

As far as I know, range names are all global in OO: the names are visible from every sheet. LibreOffice Calc, supports names that only apply to the current sheet. Maybe someone else here knows for sure.

You may also run into problems if you save your sheets as .xls instead of OO's native ODF format (.ods for spreadsheets).

[B Marcelly]

Apache OpenOffice 4.0 supports names restricted to a sheet, but only for compatibility with Excel (or LibreOffice). There is no user interface to declare a name restricted to a sheet.

Maybe the document was created/modified as an Excel spreadsheet (xls). Before AOO 4.0 the names were handled as global by OpenOffice. Now OpenOffice recognizes that the names are specific to a sheet.

Information for application developers : the API has added interfaces XnamedRanges2, and XNamedRange2, and structure com.sun.star.sheet.RangeScopeName. The API for LibreOffice is different.