https://bugs.documentfoundation.org/show_bug.cgi?id=156809
Bug ID: 156809
Summary: CALC VLOOKUP() & HLOOKUP() column or row insertion
corrupts references - they do not expand
Product: LibreOffice
Version: 7.4.7.2 release
Hardware: All
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: that.man.co...@gmail.com
Description:
When a VLOOKUP() or HLOOKUP() array is modified by the insertion or deletion of
rows or columns the target reference pointing into the array is not updated
with the new row or column reference.
That is to say if the formula is VLOOKUP(E1:H12;"Row4";3) and a new column is
inserted before the third column"G" -this being the target "3" then the
contents of column G are moved to column F or "4" in targeting terms but the
formula continues to point at column 3 which is now blank.
that is to say Formula(ic) references to coordinates in a VLOOKUP() or HLOOKUP
table are not updated to the new array coordinates if the target array is
modified after the formula has been created.
YES - MENU>TOOLS>OPTIONS>CALC>GENERAL>INPUT SETTINGS expand references when
columns/rows are inserted has been selected/activated.
Steps to Reproduce:
Using the attached CALC
Observe the formulae and values in A4:A5
Right-click column header "G" and insert one column before
Observe the formula and value in A4
Right-click row header "8" and delete one row
Observe the formula and value in A5
The impact is also observable when columns are deleted and rows are inserted
Pre-naming the array and referring to the name in the formula does not
remediate.
Actual Results:
Formula is not updated to reflect the new coordinates in the Target array
Expected Results:
Formula should be updated
Reproducible: Always
User Profile Reset: No
Additional Info:
Version: 7.4.7.2 (x64) / LibreOffice Community
Build ID: 723314e595e8007d3cf785c16538505a1c878ca5
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: sv-SE (en_GB); UI: en-GB
Calc: threaded
--
You are receiving this mail because:
You are the assignee for the bug.
