Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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 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

validity for A2

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

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 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

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.