Download it now!
Bug 82928 - Lookup formulae don't update when sheet is sorted
Summary: Lookup formulae don't update when sheet is sorted
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.6.2 release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-08-22 00:25 UTC by Mike B
Modified: 2015-01-05 05:57 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Mike B 2014-08-22 00:25:19 UTC
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
Comment 1 ign_christian 2014-08-22 04:25:51 UTC
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
Comment 2 Jean-Baptiste Faure 2014-08-22 04:53:58 UTC
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
Comment 3 Jean-Baptiste Faure 2014-08-22 04:55:58 UTC
Removed viewing in the summary: the issue is about sorting.

Best regards. JBF
Comment 4 Mike B 2014-08-22 06:16:16 UTC
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.
Comment 5 Mike B 2014-08-22 06:19:41 UTC
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.
Comment 6 Jean-Baptiste Faure 2014-08-22 08:17:19 UTC
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
Comment 7 Mike B 2015-01-02 23:46:43 UTC
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.
Comment 8 Alex Thurgood 2015-01-03 17:38:51 UTC
Adding self to CC if not already on
Comment 9 Jean-Baptiste Faure 2015-01-05 05:57:04 UTC
(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