Issue 23098

Summary: function wizard need to sometimes use absolute references
Product: Calc Reporter: ericinterplascom <eric>
Component: editingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: frank, issues, kpalagin, niklas.nebel
Version: OOo 1.1   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

Description ericinterplascom 2003-12-02 14:18:49 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.
Comment 1 frank 2003-12-02 16:27:22 UTC
*** Issue 23099 has been marked as a duplicate of this issue. ***
Comment 2 frank 2003-12-05 12:10:55 UTC
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
Comment 3 frank 2003-12-05 12:11:16 UTC
closed wfm
Comment 4 ericinterplascom 2003-12-05 15:32:28 UTC
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.
Comment 5 ericinterplascom 2003-12-08 06:24:30 UTC
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.
Comment 6 frank 2003-12-08 14:53:23 UTC
Hi Niklas,

please give a comment.

Frank

PS Target not set and not confirmed because I think we work as we should.
Comment 7 ericinterplascom 2003-12-09 15:10:28 UTC
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.
Comment 8 niklas.nebel 2003-12-19 16:14:24 UTC
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 ***
Comment 9 niklas.nebel 2003-12-19 16:15:05 UTC
closing duplicate.
Comment 10 kpalagin 2007-02-06 09:42:51 UTC
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.
Comment 11 andreschnabel 2007-03-16 14:17:08 UTC
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.
Comment 12 kpalagin 2009-11-10 16:18:30 UTC
Dear developers,
please consider this issue to be taken out or "requirements" getto.

Thanks a lot for your attention.
WBR,
KP.