Migrating from Excel and indirect formula not working in Calc

SouthernWolf · April 30, 2020, 8:14pm

I am trying to migrate away from Microsoft Office and use LibreOffice exclusively, however, I am running into a huge problem.

One of the formulas that I rely on is not working in LibreOffice Calc.

INDIRECT(SUBSTITUTE(E6," “,”_"))

I am attempting to use this with data validity.

In column E6 there is data validity that pulls from our Business_Subsidiaries table. The returned business name is just like we would type it without the “_” in place of the spaces.

Where I am running into trouble is trying to use data validity in column F6. It needs to look at the business name at E6 and then return the data from the named table associated with that business.

What the spreadsheet sees is “Our Business” in location E6, but the actual table name is “Our_Business”. When I select “cell range” under validity and input the above formula as the “source” I get a return of #REF!

It seems that the function “substitute” is not swapping " " for “_” when searching for the named table like it did in Excel.

How can I solve this other than turning the business names we see to Our_Business_1, Our_Business_2, and so on?

anon73440385 · April 30, 2020, 8:52pm

If E6 contains Our Business → SUBSTITUTE(E6," ","_") yields: Our_Business; But this is only a valid reference, if you have a named range called Our_Business (it is not the name of a sheet but a named range). Did you check in Navigator (View -> Navigator or F5) under Range names, whether the names of named ranges did correctly migrate on opening Excel file wit Calc?

SouthernWolf · April 30, 2020, 9:27pm

@anon73440385 - I am completely rebuilding my excel sheet in calc. Some of my excel options were causing problems in calc.

SouthernWolf · April 30, 2020, 8:37pm

FOUND THE ANSWER…

for LibreOffice you have to replace the comma’s in the formula above with semicolons.

Do ALL formulas in LibreOffice Calc have to be separated by semicolons instead of commas?

erAck · April 30, 2020, 8:47pm

That depends on your locale and settings. Semicolon always works regardless of setting, which is why we use it here in examples. Whether comma could even work depends, if your locale uses comma as decimal separator then it can’t. See Tools → Options → Calc → Formula, Separators, Function.

SouthernWolf · April 30, 2020, 9:25pm

Thanks for the tip

erAck · April 30, 2020, 8:43pm

SUBSTITUTE() works fine, you can check by entering the formula =SUBSTITUTE(E6," ","_") in a cell and see the result should be Our_Business. The argument of INDIRECT() has to be a cell address/range or a named range. Make sure the string returned by SUBSTITUTE() “Our_Business” is actually a valid named range existing in your document. View in Sheet → Named Ranges and Expressions → Manage (Ctrl+F3).