Data Validation Using Double Dependent Dropdown List – LibreOffice Calc

9695 views
9

This tutorial would explain how to create a dependent dropdown list in LibreOffice Calc.

In the earlier article, a single dropdown list is explained with data populated from a range inside the sheet. If you are new to dropdown lists data validation, you may read this first.

Data Setup

Say we have two lists containing values of their respective categories like this.

  • Colors
    Orange
    Blue
    Black
    White
    Green
  • Planets
    Pluto
    Mars
    Earth
    Venus
    Jupiter

We will have the first dropdown which lists the categories i.e. Colors and Planets.
Based on what user choose in first dropdown, we want to fill the second dropdown with the list of items of the respective chosen dropdown.

I.e. If you choose Planets, the second dropdown would show a list of planets, instead of list of colors and vice versa.

Data Setup for Dependent Dropdown
Data Setup for Dependent Dropdown

Steps for Data Validation Using Double Dependent Dropdown List

  • Select the categories i.e. A1 and B1. Then from menu, select Sheet -> Named ranges and expressions -> Define.
define named ranges-1
define named ranges-1
  • Put name as category1. Click Add.
  • Likewise, select A2 to B6 and add named range as category2.
  • Select E1 and from menu select Data -> Validity.
    In the validity window, choose Allow=Cell range and put category1 as source. Press OK.
  • Select E2 and from menu select Data -> Validity.
    In the validity window, choose Allow=Cell range and put below as source, then press OK.

INDEX(category2,,MATCH(E1,category1,0))

  • Now, click on the small dropdown in E1 cell, you can see category1 i.e. colors and planets would be listed from A1:B1 cells.
  • Select Planets.
  • Now click E2 dropdown, you can see only planets values are loaded in this dropdown depending on the value of E1.
  • Go back to E1 dropdown and select Colors. And likewise open up the E2 dropdown. You can see, it contains the colors values dependent on the E1 value.

So this is how you can create dependent dropdown in LibreOffice Calc.

Explanation of INDEX and MATCH in second validation

INDEX(category2,,MATCH(E1,category1,0))

MATCH : It returns the relative position of the value selected in E1 after searching in category1 range (A1:B1). Last argument 0 – denotes it would look for an exact match.

INDEX: returns the subset of an nXn range from reference category2 based on row and column number. The second argument is the row (which is omitted here as values are present in columns) and the third argument is column number.

Watch this tutorial on Video

I have made the following video after many questions on Error 501 with above instructions. The instructions are correct. Follow the steps exactly mentioned above.

Still have questions? Drop a comment below.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.


arindam

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ivan

Doesn’t work for me. Keep getting an error 501…

chris

Same here, Error 501 on the second drop down.

Carlos Araujo

I deeply respect and admire the genious minds behind this and the bravery of “loosing” time to help people in trouble with doubts or problems. Thank you people of Excellence !

Victor van Zelm

Causes a error 501, not able to find out why. Please respond

WanTuh

Thank you very much for your great explanations!
I have a question concerning my use-case of this method.
I’m working on a databank with several categories and sub-categories, therefore i need to have this dependent drop-down-lists in every cell of the sub-category column.
The first step works perfect, but of course when inserting the INDEX and MATCH formula, I want the subcategory cell to match the category cell in the same row. I’m looking for a possibility to define this for the whole column, since the loads of data make it impossible to do it manually. Does anyone have an idea how to do this?
Thank you loads in advance!

Nicu

Wow, Is working perfectly! I was looking long time for this explanations.
Thank you so much!

9
0
Would love your thoughts, please comment.x