Inserting Sheets Breaks Named References in Formulas

[fewilcox]

I’ve used OpenOffice exclusively since way back when it was StarOffice 5, but recently learned that it has basically been abandoned so I’m switching over to LO. Many of the differences are frustrating (a traumatic brain injury affected my memory, so having to adjust my workflow is much harder for me than for most people), but I’m getting there. But there’s a big one right now that’s making life difficult.

I have variations of the following formula on a sheet in my D&D Character Creator spreadsheet:

=“Level 4”&T(STYLE(“Default Centered”))&T(IF(ISNA(VLOOKUP(Class1,DataClass,1,1)),"",(IF(Class1Lvl>=4,(IF(VLOOKUP(Class1,DataClass,46,1),STYLE(“Is Proficient Plain”)))))))

As you can see, being a programmer makes me inclined to use named references, especially in documents like this one that have lots of lookup tables (which are on sheets 7-9 of 9), but I ran into a problem with them in LO that I don’t in AOO.

I decided that I want to try a major change to that sheet, but without losing what I have now in case it doesn’t work out. When I copied the sheet to the right of itself both sheets suddenly became unable to find their data tables even though they are named ranges. When I tried the same maneuver in AOO it worked as expected: all of the formulas work on both copies of the sheet. When I load it up in LO again both sheets are still functional. So why wouldn’t LO let me make the copy in the first place?

Because I couldn’t think of anything else I decided to try inserting “$” before the named references, but that of course gave me a syntax error, so now I’m out of ideas. Or should I put in a bug report? Obviously I can just Save As and try my changes in a new document, but that’s a workaround – Id’ rather have a solution.

[erAck]

You didn’t say which version you use. Other than that, things depend on what the references in your named ranges or named expressions look like, whether a sheet reference is included or not and if so whether it’s relative or absolute (relative sheet references stay relative, so when used on a different sheet they point to another sheet). Furthermore it depends on whether the named expression is of sheet-local or global scope. Copying a sheet to another sheet, global named expression are created as sheet-local names if they point to the sheet copied (something AOO doesn’t know and doesn’t do) and thus should work as expected. But maybe it’s yet something different. Best take a look at the 5.2 release notes section that gives examples.

You probably can fix things if you use absolute sheet references in your named expressions (assuming you don’t copy the lookup table sheets as well in the same order relative to the copied formula sheet).