My process is as follows:
1: Use Manage Names to import data from an external worksheet.
2: Then use Data Validity to create a drop-down list and allow only certain terms from that sheet.
3: Finally use a VLOOKUP to find the corresponding data to 2 (it finds the value in column A in the external worksheet, then returns the corresponding value from column B in the external worksheet).
This all works wonderfully, until I save, close and reload the worksheet (the one that does the above process, not the external worksheet).
On the reload, the names in Manage Names are correct (step 1, above). The Data Validity appears to be configured correctly and the data in the validated cells (step 2, above) also appears fine. But the VLOOKUP (step 3) returns #N/A.
If I reselect the data in the validated cells (step 2), or if I click to edit the data and highlight ALL the text then press the return key, the VLOOKUP starts working again immediately. So I think the problem may be due to the way the text is stored - perhaps some form of hidden character or encoding issue?
I have AutoCalculate enabled and Update Links When Opening set to Always.
Any idea how I can get this working automatically again? It was working fine in OpenOffice but when I switched to LibreOffice yesterday it stopped working. This is a really dangerous problem for me as we use this spreadsheet for creating customer quotations…
EDIT: I have found this happens when saving as .XLS format (and .XLSX causes even bigger problems!) but saving as .ODS seems to work fine. As this is for internal use only it should be sufficient for my current needs.