Intersection value using named ranges and INDIRECT()

English
, ,
#1

Hello, I’m a new Libre Office user migrating from MS Office. In Excel, it was common practice for me to create tables of data with both column and row headers. I would then create named ranges for each column and row using the header name. I could then locate the value of the intersections with a formula such as “=INDIRECT(cell with column header name) INDIRECT(cell with row header name)”. Works great in Excel but I am getting Err:509 on all of these cells in Libre Office. Any tricks or alternatives here? TIA

Edit: attached is a file that shows an example working in Excel but not in LibreOffice.
ExampleFile.xlsx (6.3 KB)

Get value of intersection from an array
#2

A sample Excel file with such a formula, working in Excel, but not in Calc, would be better than a thousand words :slight_smile:

#3

Done. Thank you.

#4

In LibreOffice it needs an exclamation mark between the two INDIRECTs, see Operators in LibreOffice Calc

Works in Excel 2010 without modification so could be a bug in the import filter, How to Report Bugs in LibreOffice - The Document Foundation Wiki

#6

Simply adding the ! in between has worked. Thanks so much!

#7

I added bug for .xlsx import filter
Bug 160371 - import filter xlsx does not replace MS intersect operator with LibreOffice intersect operator

Note that the .xls import filter correctly converts the space to an exclamation mark.

#5

tested:
01_LO-CALC_distribute-selected-values.ods (18.4 KB)

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