Lotus .123 Import Issues - Formula Edits, RangeNames, @XINDEX

English
, ,
#1

Pleased to see LO Calc has improved import/open .123 files but there are still quite a bit of formula editing to do on import eg of external VLOOKUP refs. We have 100s of .123 to import and wondered if there are any pathfinding examples of the best way to do all such post-import edits/conversions ? (maybe there are helpful LO “conversion” macros ? any development plans to improve upon .123 import formula compatibility in forthcoming releases ?)
Two “killer” features that keep us bound to Lotus .123 (far too long) are:

  • (i) RangeNames that allow special characters eg “GDPn$_g” or “GDPn%£_WD” (we work with column data expressed in many different currencies in same sheet)
  • (ii) @XINDEX(range;column-heading;row-heading;[worksheet-heading])
    Returns the contents of a cell located at the intersection specified by column-heading, row-heading, and worksheet-heading.
    (i) “$” or “£” seem illegal in RangeNames in LO Calc v7.4 - any workarounds/hacks or specialist extensions that may overcome this constraint ?
    (ii) I’m aware of the nasty Excel hack by nesting two =MATCH() inside an =INDEX() but wondered if LO Calc has a more elegant solution ? (BTW what are the formula/character limits on Calc cell formulas ?)

Most kind

#2

Calc is pretty much the same as Excel as far as formula syntax is concerned.
Cross posting: Apache OpenOffice Community Forum - Lotus .123 Import Issues - Formula Edits, RangeNames, @XINDEX - (View topic)

#3

(i) No. Anything not a Unicode letter or digit or the underscore is not accepted as part of a name. In case of GDPn%£_WD the % would also be the percent operator, even if £ was accepted. Bear i mind that the name must be usable as identifier in a formula expression.

#4

Most kind - thanks. I’m intrigued how an “old” product like Lotus 123 allows SCs like $ and % in RangeNames but more “advanced” Excel-style software cannot ?!..Lotus must be “escaping” these SCs somehow ?

#5

Maybe it just doesn’t know the % percent operator in formula expressions, and further allows anything that could not possibly be a cell address or range in only its own known grammar, which is limited.

#6

There is nothing “advanced” in Excel and Calc.
(ii) @XINDEX(range;column-heading;row-heading;[worksheet-heading]) may be substituted with the range label feature.
RowCol_Labels.ods (16.7 KB)

1 Like
#7

Lotus knew the restrictions of Excel at the time and could make suitable conversions. You might find that the most accurate method for exporting to Calc is open the .123 file in Lotus 123 and Save As .XLS and then open the xls in Calc.

1 Like
#8

Most kind. The RowCols labels solution seems only to work with strings as Col/Row headers(?) - @xindex works with either string/numbers as Col/Row headers (very handy querying raw dumped data that has Date in numeric format across columns eg 2010,2011,2012… ) and can accept a multi-sheet lookup range. Anything similar to this in LO Calc ? - maybe an 3D Array Formula version of =INDEX ? or a custom function via BASIC ?

#9

Expanding my sample sheet to some more sheets in the same document, I can’t find any solution referencing mulltiple sheets by row/col labels. Labels used in a formula seem to refer to the same sheet where the formula is in.

Excel and Calc prefer a database-like approach (“normalized tables”) over cross-tables. In the end “everybody” loves to use spreadsheets as cheap database surrogate.
Instead of the cross table in my sample sheet, the following table contains the same information.
This way you can merge the data from many cross tables on separate sheets into one database-like table which can be analysed by simple formulas and anything under the “Data” menu.
Since LibreOffice includes a database component you may also transfer your data to a relational database where they are kept safe and warm while being very easy to sort, filter and aggregate. Where the database arithmetics do not suffice, you can seamlessly link database data into Calc sheets.
Normalized table with info from 2 sheets:

Sheet Product Person Value
Sheet1 Apple Charlie 94
Sheet1 Pear Charlie 67
Sheet1 Orange Charlie 67
Sheet1 Banana Charlie 82
Sheet1 Apple Anne 76
Sheet1 Pear Anne 71
Sheet1 Orange Anne 62
Sheet1 Banana Anne 78
Sheet1 Apple Michael 60
Sheet1 Pear Michael 58
Sheet1 Orange Michael 74
Sheet1 Banana Michael 90
Sheet1 Apple Ayshe 85
Sheet1 Pear Ayshe 97
Sheet1 Orange Ayshe 63
Sheet1 Banana Ayshe 51
Sheet2 Apple Charlie 194
Sheet2 Pear Charlie 167
Sheet2 Orange Charlie 167
Sheet2 Banana Charlie 182
Sheet2 Apple Anne 176
Sheet2 Pear Anne 171
Sheet2 Orange Anne 162
Sheet2 Banana Anne 178
Sheet2 Apple Michael 160
Sheet2 Pear Michael 158
Sheet2 Orange Michael 174
Sheet2 Banana Michael 190
Sheet2 Apple Ayshe 185
Sheet2 Pear Ayshe 197
Sheet2 Orange Ayshe 163
Sheet2 Banana Ayshe 151
#10

Much appreciated - your kind efforts imply an =XINDEX() function will be a really useful addition to the standard lookup functions in future versions of LO Calc - to avoid the db workarounds you suggest. I notice Quatto Pro Spreadsheet is the only other package - than Lotus 123 - to have a built-in XINDEX(). Its a bit surprising that no-one has produced a custom function to achieve the 3D lookup capability that XINDEX() brings. Thanks again

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.