Create Dynamic Dropdown Lists in LibreOffice Calc

Dynamic dropdown lists in LibreOffice Calc allow the options in a secondary dropdown menu to automatically change based on the selection made in a primary dropdown menu. This tutorial provides a straightforward, step-by-step guide to setting up dependent dropdown lists using named ranges and the INDIRECT function to ensure accurate and efficient data entry in your spreadsheets.

Step 1: Organize Your Source Data

  1. Open a new sheet (e.g., named Data) to store your categories and dependent items.
  2. In row 1, enter the main category headers across separate columns (e.g., Fruits in cell A1, Vegetables in cell B1).
  3. Under each header, list the corresponding items (e.g., list Apple, Banana, Orange under Fruits; list Carrot, Broccoli, Spinach under Vegetables).

Step 2: Define Named Ranges

LibreOffice Calc needs named ranges matching the main category names exactly to dynamically link the lists.

  1. Select the items under your first category (e.g., select A2:A4 for Fruits).
  2. Go to Sheet > Named Ranges and Expressions > Define… (or press Ctrl + F3).
  3. In the Name field, enter the exact name of the category header (e.g., Fruits).
  4. Click Add, then click OK.
  5. Repeat this process for each category column, ensuring the named range matches the header text exactly (avoid spaces; use underscores like Root_Vegetables if necessary).

Step 3: Create the Primary Dropdown List

  1. Switch to the sheet where you want the user to input data.
  2. Select the cell where you want the main category dropdown (e.g., cell A2).
  3. Go to Data > Validity… in the top menu.
  4. In the Criteria tab, set the Allow dropdown to Cell range (or List).
  5. In the Source box, select the header cells from your data sheet (e.g., Data.$A$1:$B$1).
  6. Click OK. Cell A2 will now display a dropdown with your main categories.

Step 4: Create the Dependent Dynamic Dropdown List

  1. Select the cell directly adjacent to your primary dropdown (e.g., cell B2).
  2. Go to Data > Validity….
  3. In the Criteria tab, select Cell range under the Allow field.
  4. In the Source box, enter the following formula: INDIRECT(A2) (Replace A2 with the cell reference of your primary dropdown).
  5. Click OK.

Step 5: Test the Configuration

Select an option from the primary dropdown in cell A2. Then, click the dropdown in cell B2. The list in B2 will automatically populate only with the items associated with the selected category in A2. If you change the selection in A2, the available choices in B2 will update dynamically.