This worked in the 4.1 series and before. Columns A thru E or F contain data. Columns F-G or G-H contain lookups to a database page in the currently open file of the form: [Col. F] =IF(D10<100," ",VLOOKUP(D10,$Accounts.$A$1:$B$139,2,0)) [Col. G] =IF(E10<100," ",VLOOKUP(E10,$Accounts.$A$1:$B$139,2,0)) When columns A thru E (or F on another page; adjust formula to match) are sorted, even using F9 to refresh the sheet, the lookups are not updated. If I save/close and reopen the file, the lookups update correctly. When data are originally entered in columns A thru E (or F), the lookups also update correctly. Manually changing the contents of the "key" cell also updates correctly. Apparently, if "key" references used by the formula are moved while the file is open, LO 4.2 ignores the change until the file is closed and reopened. Other times, it updates correctly. This is an error. This worked OK in 4.1.x with formulae as written; it also worked OK without explicitly including the final '0' parameter in 3.x - the added parameter became necessary in 4.x because it no longer treats the default condition in the Excel fashion. Operating System: Windows 7 Version: 4.2.6.2 release
Hi Mike.. If it's about sorting with internal reference to other sheet, I guess it's related to Bug 79441. Please try daily build to check whether it's fixed: http://dev-builds.libreoffice.org/daily/ Informations about testing daily build: http://wiki.documentfoundation.org/Testing_Daily_Builds Then please post your test results here
Question asked in comment #1, so set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding. Best regards. JBF
Removed viewing in the summary: the issue is about sorting. Best regards. JBF
Behavior did not change in 4.3.2 of Aug. 21 2014. Sort of data still does not trigger update of lookups and, it turns out, another related cell that just does some simple arithmetic. Workaround (to avoid save-exit-restart LO cycle) is to copy the related cells for the first line of data (which usually are OK) and copy them over the others. Copying the formulae does trigger the refresh. F9 manual refresh does nothing in this case, either. The VLOOKUP concern is probably a red herring. The real issue seems to be the failure to trigger a refresh of related cells when cell contents change due to a sort.
Edit to the last comment: the workaround is to copy the cells related to the first line of data, the PASTE over the others down to at least the end of the data. The paste seems to trigger a refresh after which everything displays correctly again.
Not sure if we are speaking about the same thing. So please attach your own test file and describe step by step what to do to reproduce the problem. Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding. Best regards. JBF
I updated to 4.3.5.2 last weekend, and the problem now is gone. Sorts are quicker, and all related (lookup) cells update correctly. So the bug (if it really was a separate one) is gone. Can be closed.
Adding self to CC if not already on
(In reply to Mike B from comment #7) > I updated to 4.3.5.2 last weekend, and the problem now is gone. Sorts are > quicker, and all related (lookup) cells update correctly. So the bug (if it > really was a separate one) is gone. Can be closed. Thank you for the update. Closing as worksforme because the issue was not confirmed and we don't know what solved it for the reporter. Best regards. JBF