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?