Ask Your Question
0

Drop down box and database range question

asked 2017-12-14 15:19:50 +0200

jnac91 gravatar image

Hey,

I'll start by saying I don't have a lot of experience so please forgive me if this is simple and I just haven't figured out how to search a solution for my problem.

I have a cell in sheet 1 (A1) set up as a drop box linked to a data range in sheet 2. For example the drop box lists computer models (Dell, Lenovo, Acer etc.) I also have data ranges set in sheet 2 for all the models from each manufacturer.

Is there a way to set the next cell (B1) to display a drop down of the data set relating to the selected model? For example if I were to select "Dell" from the drop down in A1 the drop down in B2 would show the data set for Dell and if I changed A1 to "Acer" it would change the B1 drop down to the data set for the Acer models.

I hope this makes sense, I guess I would call it a filter but in drop down menu form. The over all goal is to be able to select Make then Type then Model then specs. If I were to use one drop down for all models it would be hundreds of lines long and efficient.

Am I asking too much?

Thanks in advance.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-12-14 19:10:44 +0200

Ratslinger gravatar image

updated 2017-12-14 19:12:06 +0200

Hello,

The are a certainly number of way to do this but this sample ( click here - for "Price Sheet.ods") uses INDEX to access the the Data Ranges. It contains three dropdowns with the last two successively based upon the previous selection.

edit flag offensive delete link more

Comments

Thanks for that, I'm sure it will come in handy when trying to implement this.

jnac91 gravatar imagejnac91 ( 2017-12-15 08:49:25 +0200 )edit
0

answered 2017-12-14 18:54:17 +0200

Jim K gravatar image

updated 2017-12-14 18:59:25 +0200

Use formulas to determine what goes in the cell range that the second drop-down list comes from. The formulas will be based on which company is selected.

As an example, set up sheet 2 like this.

A           B        C        D  E
~~~~~~~~~~  ~~~~~~~  ~~~~~~~  ~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dell        Lenovo   Acer        List for currently selected company
Latitude    Lmodel1  Amodel1
Inspiron    Lmodel2  Amodel2
OptiPlex    Lmodel3  Amodel3
Precision

Then in cell E2, use this formula. Fill down to E10.

=IFERROR(HLOOKUP($Sheet1.A$2;A$1:C$5;ROW());"")

Optionally, add this in F1.

=Sheet1.A2

Now on Sheet 1, go to cell A2 and do Data -> Validity.

  • Allow: Cell Range
  • Source: $Sheet2.$A$1:$Sheet2.$C$1

validity for A2

Finally, go to cell B2 and set Data Validity to allow cell range $Sheet2.$E$2:$E$10.

While this works in Calc, the interface seems a bit awkward. LibreOffice Base forms are better.

edit flag offensive delete link more

Comments

Thank you very much, great answer :) Exactly what I was looking for.

jnac91 gravatar imagejnac91 ( 2017-12-15 08:47:15 +0200 )edit

@jnac91: Glad this helped. Please click ✔ in the upper left of the answer to mark it correct, as described in guidelines for asking.

Jim K gravatar imageJim K ( 2017-12-15 15:04:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-14 15:19:50 +0200

Seen: 438 times

Last updated: Dec 14 '17