Issue 107377 - vlookup does not update index when inserting/deliting columns
Summary: vlookup does not update index when inserting/deliting columns
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 3.1
Hardware: Unknown All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2009-12-02 11:14 UTC by oger000
Modified: 2016-11-28 00:38 UTC (History)
3 users (show)

See Also:
Issue Type: ENHANCEMENT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Example to explain what is done (9.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-12-02 11:16 UTC, oger000 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description oger000 2009-12-02 11:14:56 UTC 
If you insert/delete a column between the first column and the colum referenced
by the index (offset), the index-count is not updated. All other cell references
are updated.

Because its hard for me to explain I attac a simple example. Open it mark column
"C" and write-click and do a "insert column". The value in line 20 calculated by
the vlookup function should not change, but does because the index is not
adjusted from "4" to "5". Same happens when inserting cells with "move to
right". And (untested) the same will be valid for hlookup.


MS-office-2000 has the same behavour (which makes it not better). Newer versions
of MS-office I do not have at hand to test.
Comment 1 oger000 2009-12-02 11:16:23 UTC 
Created attachment 66451 [details]
Example to explain what is done
Comment 2 Regina Henschel 2009-12-02 18:19:00 UTC 
Both Gnumeric 1.9.10 and Excel 2007 behave the same. But I can imagine, that OOo
holds a list of ranges which are used in VLOOKUP or similar functions and give a
warning if inserting or deleting affects such a range.

valid feature request
Comment 3 haliastur.indus 2016-11-28 00:18:32 UTC 
This issue is still present in version 4.1.3, the latest version. I just downloaded it and tested it.
It's a problem because if you weren't aware of the bug, or if there was nothing in your spreadsheet to cause you to notice that this bug had made your spreadsheet results invalid/ incorrect, your results would be wrong and you might make serious financial decisions based upon incorrect information. (Which I nearly did.)
Comment 4 mroe 2016-11-28 00:38:12 UTC 
A static number should NEVER change! A programmer should know what he is doing.

In the example simply change the formula from
=VLOOKUP(A20;A6:E8;4;FALSE())
to
=VLOOKUP(A20;A6:E8;COLUMNS(A4:D4);FALSE())
to make the value non static.