[CLOSED ABANDONED] Calc: named ranges v database ranges

[taxedserf]

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.

[erAck]

.

0. state your LibreOffice version
1. “cannot understand” exactly means what? The formula is …? The error is …?
2. the named range is defined exactly how? global scope? sheet scope?
3. 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.

[taxedserf]

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.

[Sensor]

I have the same problem, which is in fact a conceptual problem. I am coming from Excel and try to understand what are Calc’s named-ranges vs. databases. Where can I read what the story on this is?

Practical problem I run into: I am used to calling my databases (i.e. areas that are being sorted again and again) “Database” - not global but local to that sheet. But it seems in Calc I can have only one database-range with the name “Database” per workbook. Is that correct?

So now I revert to calling the databases on the different sheets in one workbook “DB_Sheet1” etcetera, is that the best way?

Win10 / LibreOffice 6.3.3.2.

[Lupp]

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.