Vlookup conversion issue when opening/converting a libreoffice calc workbook to excel

[Rockerdo]

I posted this this am but it is gone.

I have recently done several large projects. Since many of our sites do not have full Excel, I had to convert some of the workbooks to be able to work in LibreOffice Calc.

The folks that are using the Libre versions will save the workbook as an XLSX (or XLS) file since the processing center uses full excel. I get that there are differences between Libre and Excel.

The problem is in the conversion. Several of my Vlookups are NOT working when opening the converted Libre workbook.

The Vlookup and formulas are written exaclty the same in the excel version as the Libre version.

When I look at the Vlookups in the converted Libre version ----Excel has converted them to a Table and the table tool shows up. My named vlookup are no longer in my formula name manager so my formulas are not working.

Converting these tables back to range each time and renaming them is not practical. There are 3 major Vlookups that cover well over 1200 formulas in this workbook. Some of the minor ones not working is not a big deal. These 3, though are.

Since these are relatively easy vlookups, why are they not converting? I'm trying hard to understand why so I can find a solution to make this as easy for my folks as possible. Getting them all full excel is not going to happen. I wish it could. I want to be able to open these in excel to process them. Those that are opening these libre converted workbooks can see the information until they save the workbook or click to enable editing. Once they do that it disappears.

Most of the processors for these completed workbooks that are sent to them have 2010 on their systems. Some have 2013, but I'd say most are still working in 2010.


One of the larger Vlookups is 309 rows by 2 column. Moderately long but not complicated. If I open the converted workbook in Libre they are still there. If I open them in Excel poof, the named vlookup is gone.

Anyone have any idea why for these Vlookups excel is converting to a table and not reading it as a vlookup any longer? If no solution on how to prevent this, I'll explore more ideas. If it is just a true compatibility issue there simply may be no solution.

I put a great deal of work in these 4 workbooks and want them to function correctly. I was just hoping someone here would have an idea of what to try.

 

[mole999]

what happens if you use the normal lookup, rather than naming a range

do any named lookups work ?

 

[Rockerdo]

Do you mean in data validation? Not really, but they aren't as critical. The ranges I built for the drop downs still show in the XLSX workbook if it is open in Libre. If I open it in Excel, my named lookups are not working. I expected data validation and conditional formats to not fully carry over. I had this issue in testing it and in build. Those aren't as critical. The value for the codes that are picked are still visible in Excle, just the drop down to change them are gone. But not critical.

this is one of the vlookups =IF($G27="No",0,IF($A27<0,"",IF(ISERROR(VLOOKUP($A27,VUnitsallowed,2,0)),"",(VLOOKUP($A27,VUnitsallowed,2,0)))))

The named look up is 309 rows. This returns the value. If I put the value in the drop down, we will get too many errors since folks can't seem to grasp the concept of how many units are allowed based on the time they enter so the vlookup does it for them. This is key information to process the changes needed based on what they fill out. Without it the tool really is useless.

 

[Youngdand]

I think what mole is getting at, is instead of using a named range in your vlookup, can you not use the absolute reference of the range instead within your vlookup. Eg $A$1:$A:25 if the range is on another sheet, you would need to reference the sheet before the range.