Drop down box and database range question


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.

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

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


Optionally, add this in F1.


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.

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

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


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.

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