Apache OpenOffice (AOO) Bugzilla – Issue 23098
function wizard need to sometimes use absolute references
Last modified: 2013-02-07 22:34:33 UTC
> Series: > 3. When doing vlookup, and dragging a series, you'll notice the first > parameter increases as it should but that lookup array reference parameter > also increases: > > =VLOOKUP(K2;'City Abbreviations'.D1:E1039;2;0) > =VLOOKUP(K3;'City Abbreviations'.D2:E1040;2;0) > > I could do a SHIFT-F4 to convert a cell from relative to absolute: > =VLOOKUP(K2;'City Abbreviations'.D1:E1039;2;0) > becomes: > =VLOOKUP($K$2;'City Abbreviations'.$D$1:$E$1039;2;0) > but I have to manually edit the first paramenter: > =VLOOKUP(K2;'City Abbreviations'.$D$1:$E$1039;2;0) > > IMHO, the function wizard should have made the array (the second parameter) > absolute addresses instead of relative. In fact, there may be more > functions should be made absolute upon function creation by the Autopilot.
*** Issue 23099 has been marked as a duplicate of this issue. ***
Hi, if you use SHIFT+F4 more than once, you can change all parameters from absolute to relative and vice versa with all combinations possible. So I've to close this one as worksforme. Frank
closed wfm
Firstly, the Aboluteness should be a default behavior when a lookup array (the second parameter) is concerned for the [VH]LOOKUP functions: Create Sheet1: FNAME LNAME BOB BARKER ERIC WOOD TOM BROKAW Create Sheet2: FNAME LNAMEFORMULA TOM =VLOOKUP(A2;Sheet1.A1:B4;2;0) ERIC BOB Starting with what the AutoPilit generates: =VLOOKUP(A2;Sheet1.A1:B4;2;0) Cycling through the SHIFT-F4's: =VLOOKUP($A$2;$Sheet1.$A$1:$B$4;2;0) =VLOOKUP(A$2;$Sheet1.A$1:B$4;2;0) =VLOOKUP($A2;$Sheet1.$A1:$B4;2;0) =VLOOKUP($A2;$Sheet1.$A1:$B4;2;0) =VLOOKUP($A$2;Sheet1.$A$1:$B$4;2;0) =VLOOKUP(A$2;Sheet1.A$1:B$4;2;0) =VLOOKUP($A2;Sheet1.$A1:$B4;2;0) then back to =VLOOKUP(A2;Sheet1.A1:B4;2;0) But my desired formula is: =VLOOKUP(A2;Sheet1.$A$1:$B$4;2;0) I can not make a successful series without this syntax. When doing [VH]LOOKUPS, then array is essential the database *range* which should have absolute address to begin with. The range of lookup values should not change, on the lookup key which is the first parameter. It is relative based on the series you'll create. To my understanding this is how most people use [VH]LOOKUPS. The behavior in Excel works. However, even though the array paramenter is also not absolute, Excel chooses to not increment the array parameter when creating a series. Purely an implementation decision. But I feel that the second parament should show you absoluteness in the function - so there's no smoke and mirrors about behavior. So that is why some parameters of some functions should begin life in the AutoPilot as absolute parameters.
Typos corrections: 1. the array (2nd parameter) is essentially the database *range*, which should become a fixed reference. 2. only the lookup key (1st parameter) should change or remain relative.
Hi Niklas, please give a comment. Frank PS Target not set and not confirmed because I think we work as we should.
Okay, I think the real problem is that I can't have the lookup array be an entire column, the autopilot won't let me choose (highlight) the columns: Excel function: =VLOOKUP(A2,Sheet1!A:B,2,0) Calc function: =VLOOKUP(A2;Sheet1.A1:B5;2;0) What I need is the Calc function be =VLOOKUP(A2;Sheet1.A:B;2;0) As I said, Calc can't handle selecting entire columns for a array parameter value while in the Autopilot window, nor will the function work if manually entered (A:B). So I'm forcing to manually making the formula be =VLOOKUP (A2;Sheet1.$A$1:$B$5;2;0) in order to keep my lookup array constant ( note: ALT- F4 combo doesn't give this to me). Why isn't =VLOOKUP(A2;Sheet1.A:B;2;0) valid in Calc? -Eric Wood PS. So this changes to scope of the Issue, and there may already be a duplicate.
There's issue 20495 about "A:B" style references. The current plan is to convert them into normal references during input. *** This issue has been marked as a duplicate of 20495 ***
closing duplicate.
I will allow myself to reopen the issue for two reasons: 1. User does not always want Lookup functions to search full column/row. 2. Issue 20495 seems to very far away from resolution and seems to be non- trivial undertaking. On the other hand, this issue (23098) looks like trivial enough - just slightly modify how Function Wizard generates the formula (adding '$' to the cell references). After all, it seems naturall for Lookup function to work on the same array (defining range is impractical in many cases because files areregenearted every time). Please consider this enhancement. Thanks a lot.
confirmed as usefull enhancement. Althouh OOo behaves as designed, it would be usefull for several functions, if the wizard would generate absolute instead of relative cell references.
Dear developers, please consider this issue to be taken out or "requirements" getto. Thanks a lot for your attention. WBR, KP.