[Solved] Show all results of VLOOKUP in a Dropdown

Discuss the spreadsheet application

[Solved] Show all results of VLOOKUP in a Dropdown

Postby JacoSk » Tue Sep 20, 2016 3:43 pm

I have a OO Calc table which looks something like this:

Row_A Row_B
A 3123
A 2321
A 4565
B 7675
B 1292
C 9654
C 8765
C 6743
I also have another table which contains two dropdown lists. The first dropdown list shows the unique values in Row_A, so you can select fromA, B or C.

Now I want that after selecting the first dropdown value, the second one shows the values in Row_B for the selection of the first dropdown, e.g. if A is selected, then it should show 3123, 2321 and 4565.

In SQL I could simply do something among the lines of select Row_B From Table where Row_A=&1. Now I thought I may realize this by using VLOOKUP and adding the command to Data -> Validity -> Cell Range, but this only shows me one of the results (I guess depending on the order parameter), but I want all valid options. Is that possible?

By the way, if it somehow helps: I am getting the data (Row_A and Row_B) by creating an Import Range from a PostgreSQL database which I connected to the Calc file as a Base object (.odb file) , so if I can somehow skip the having-the-data-in-another-table part and creating the dropdown from the database directly, it would be amazing.
Last edited by JacoSk on Thu Sep 22, 2016 12:30 pm, edited 1 time in total.
LibreOffice 5.1.4.2 on Windows 10
JacoSk
 
Posts: 12
Joined: Mon Sep 19, 2016 11:16 am

Re: Show all results of VLOOKUP in a Dropdown

Postby Villeroy » Tue Sep 20, 2016 5:28 pm

This is database functionality. Spreadsheets are no databases.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27888
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Show all results of VLOOKUP in a Dropdown

Postby JacoSk » Tue Sep 20, 2016 5:32 pm

Well, Excel knows how to do that (see here: https://www.ablebits.com/office-addins- ... sts-excel/) so I thought there may be a way to accomplish that in OO. Is there none?

(On a sidenote: I am totally aware that OO Calc may be an inconvinient choice for my needs, but sadly I don't have a choice here)
LibreOffice 5.1.4.2 on Windows 10
JacoSk
 
Posts: 12
Joined: Mon Sep 19, 2016 11:16 am

Re: Show all results of VLOOKUP in a Dropdown

Postby Zizi64 » Tue Sep 20, 2016 5:59 pm

Well, Excel knows how to do that (see here:...


Then you must create same data structure as you seen in the linked example.

Code: Select all   Expand viewCollapse view
A     B     C
3123  7675  9654
2321  1292  8765
4565        6743


Use the row data as data source of the first Dropdown.
And you need a "preselect" cellrange with the function HLOOKUP() (or VLOOKUP() depending on the structure of your data), then must apply the results of the "preselect range" as data source of the second Dropdown.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.1; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8915
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Show all results of VLOOKUP in a Dropdown

Postby Lupp » Tue Sep 20, 2016 6:12 pm

You are not the first one asking a question to that effect here. And: There is a solution - at first apearance - which was posted now and then.
Using "cascaded validation", however, will only be a crutch, and is not clearly advisable. Therefore the advice to better use a database.

The two problems I mainly see are:
1. The ranges to use in the 'Validity' expressions need to be generated based on meta information to deliver a somehow reliable functionality. This meta information must be gathered explicitly and this will rely on some assurances. (In specific: "Tables are filled in contiguous rows of their columns." This is simply a fact using a database while it can be violated using spreadsheets.)
2. The second or further steps of the cascade will not mirror a change in the first or previous ones re-validating (and likely rejecting) an already entered value. The user can therefore be tempted to ignore a mismatch. Damage to results possible to any degree! Workarounds with this respect are complicated and probably not reliable enough.

I cannot test with Excel, but I suppose the related solutions will suffer from the same shortcomings. (Many Excel "solutions" posted everywhere, in specific those based on VBA, are rubbish, IMO. Sorry. The fix range addresses in the linked suggestion are atually not usable without imposing grave restrictions/duties on you. Sorry again. I will not write an essay about this now. )

If you want to take the risks you may study the attached example. And: Do not use VLOOKUP in this context.
Attachments
aoo85196AnotherCascadedValidation_1.ods
(9.65 KiB) Downloaded 85 times
On Windows 10: LibreOffice 6.4 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2737
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Show all results of VLOOKUP in a Dropdown

Postby Villeroy » Tue Sep 20, 2016 7:41 pm

JacoSk wrote:Well, Excel knows how to do that (see here: https://www.ablebits.com/office-addins- ... sts-excel/) so I thought there may be a way to accomplish that in OO. Is there none?

(On a sidenote: I am totally aware that OO Calc may be an inconvinient choice for my needs, but sadly I don't have a choice here)


https://forum.openoffice.org/en/forum/d ... p?id=27953
Nevertheless, all these "solutions" do not even come close to what a most simple database can do.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27888
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Show all results of VLOOKUP in a Dropdown

Postby JacoSk » Thu Sep 22, 2016 12:29 pm

I've implemented @Lupp approach and it works just fine, ofc including the limitations/problems mentioned in his post. I can live with that though. Thanks for the help everyone!
LibreOffice 5.1.4.2 on Windows 10
JacoSk
 
Posts: 12
Joined: Mon Sep 19, 2016 11:16 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests