How can I create dynamic dropdown in Libreoffice Calc?

I’m trying to make 2 dropdowns as country and its states, I couldn’t figure out how to make dynamic dropdown for list of states according to selected country in Libreoffice Calc. This is example:


You can do that with Data > Validity. Use the criteria Cell Range and enable the option Show selection list. For your country you determine the range directly. For the State you need a nested formula. The outer part is the function INDEX. The range for this function is a range, with all states of one country in a column and those of another country in the next column. The second parameter of INDEX is the row. If you leave it empty the column is taken. The third parameter is the column and that is calculated. You search the country, which comes from your country drop-down list in the column headers of the country-state range using MATCH. For an example see the attached file ValidityDropDown.ods

Sorry for delay, I got your answer and it works, thanks!