We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Migrating from Excel and indirect formula not working in Calc

asked 2020-04-30 22:14:09 +0200

SouthernWolf gravatar image

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.


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?

edit retag flag offensive close merge delete


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?

Opaque gravatar imageOpaque ( 2020-04-30 22:52:00 +0200 )edit

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

SouthernWolf gravatar imageSouthernWolf ( 2020-04-30 23:27:53 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-04-30 22:37:48 +0200

SouthernWolf gravatar image


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?

edit flag offensive delete link more


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.

erAck gravatar imageerAck ( 2020-04-30 22:47:30 +0200 )edit

Thanks for the tip

SouthernWolf gravatar imageSouthernWolf ( 2020-04-30 23:25:50 +0200 )edit

answered 2020-04-30 22:43:07 +0200

erAck gravatar image

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).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-30 22:14:09 +0200

Seen: 213 times

Last updated: Apr 30 '20