Drop down box and database range question

[jnac91]

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.

[jimk]

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

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.

[jnac91]

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

[jimk]

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

[Ratslinger]

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.

[jnac91]

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