Referencing Range names in formulas from different tabs

Discuss the spreadsheet application

Referencing Range names in formulas from different tabs

Postby merrillb39 » Fri Mar 06, 2015 2:11 am

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?
Apache Open Office 4.1.1 installed on Windows 7 - 32 bit
Posts: 4
Joined: Fri Mar 06, 2015 1:31 am

Re: Referencing Range names in formulas from different tabs

Postby RusselB » Fri Mar 06, 2015 4:06 am

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,
OpenOffice 4.1.7, LibreOffice on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Posts: 6400
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Referencing Range names in formulas from different tabs

Postby acknak » Fri Mar 06, 2015 5:43 am

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).
AOO4/LO5 • Linux • Fedora 23
User avatar
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Referencing Range names in formulas from different tabs

Postby B Marcelly » Fri Mar 06, 2015 9:15 am

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 The API for LibreOffice is different.
Bernard 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Return to Calc

Who is online

Users browsing this forum: W3C [Linkcheck] and 17 guests