# Revision history [back]

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()),"")


=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 Data Validity with cell range $Sheet2.$E$2:$E$10. While this works in Calc, the interface seems a bit awkward. LibreOffice Base forms are more powerful, and this looks like a good use case for a Base form. 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 with 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 more powerful, and this looks like a good use case for a Base form.

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()),"")


=Sheet1.A2

• 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 more powerful, and this looks like a good use case for a Base form. better. 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()),"") =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.