Ask Your Question
1

[CLOSED ABANDONED] Calc: named ranges v database ranges

asked 2018-06-16 13:27:37 +0200

taxedserf gravatar image

updated 2018-06-23 13:14:07 +0200

I'm testing migration from Windows to Linux Mint.

One test features the intra-compatibility of MS Excel and LibreOffice Calc.

I'm stuck with named ranges. In the original XLSX, there are named ranges in a spreadsheet. The workbook is synced to the Linux machine via Google Drive/grive2. Whether Excel saves as ODS or Calc opens XLSX, there appears to be a conflict with named ranges.

In Excel and Google Sheets, there are only named ranges. But in Calc, there are two versions: named ranges and database ranges.

In an XSLX/ODS originated in and saved by Excel in Windows, then opened in Calc in Linux, Calc cannot understand a named range in VLOOKUP formula where the named range is in a different worksheet in the same workbook. In Calc, the user needs to re-enter the formulae to replace the named range with:

  • either a database range (which means defining the database range first);
  • or an absolute direct reference to the cells of the range.

Why did LibreOffice Calc choose to have two types of named range, making them incompatible with the choice of both Excel and Google Sheets?

My test of this issue continues, for which understanding the results means understanding Calc's choice to use two types of named ranges.

edit retag flag offensive close merge delete

Comments

1

.

  1. state your LibreOffice version
  2. "cannot understand" exactly means what? The formula is ...? The error is ...?
  3. the named range is defined exactly how? global scope? sheet scope?
  4. provide a sample document to investigate

It's unclear what exact problem you have, in general using a named range that points to a different sheet in VLOOKUP works fine.

And btw also Excel knows such database ranges, you just may know them as Tables.

erAck gravatar imageerAck ( 2018-06-18 15:21:29 +0200 )edit

I have re-done the test (with one variation) and come up with different results.

So if the two apparently-different types of "named range" are common to both Excel and Calc, then there is something else afoot. I shall need to do further testing. For now, this thread it thus closed-abandoned.

taxedserf gravatar imagetaxedserf ( 2018-06-23 13:13:45 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-06-23 16:48:08 +0200

Lupp gravatar image

I cannot tell anything about XLSX or tables in Excel... But:

In Calc the relevant completion of DatabaseRanges as compared to ordinary NamedRanges is the ability to select a column out of the values in the range based on its label used like a FieldName in a DB entered as the 'Database field'. As I understand it this is essential for the working of all the functions listed under the category 'Database' by the FunctionWizard.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-16 13:27:37 +0200

Seen: 124 times

Last updated: Jun 23 '18