How to create multiple dependent drop-down lists in Calc

[AbTo]

I am trying to make a document that uses 3 dependent drop down lists for sub-categories. The second drop-down menu is dependent on the value in the first column, and the third drop-down should be dependent on the value in the second column. I have attached an example of what I am trying to do, which might explain it better. The error I am running into is that the third column drop down is not working.

I am using data validation with the following formula:
INDEX(Category2,MATCH(A11,Category1,0))
With Category2 being the options in the second menu, based off of the values in the first column (Category1). I tried to adapt this for the third column but it is not working.

I would like to use this function for many rows (100+), so I was not having success with the INDIRECT function as it needed to be entered individually for each row. But please correct me if this is a valid option as well.

Test.ods (12.7 KB)

Thank you for any assistance.

[JohnSUN]

Welcome!
If you are willing to reorganize your selection data, then drop-down lists can be done like this -
Test (1).ods (19.9 KB)

@AbTo

would like to use this function for many rows (100+)

As soon as there were more than 5-10 fields for selection, you were obliged to think about using a database. Look here, @Villeroy has repeatedly posted examples of ODB here, in which your problem is solved easily and elegantly

[AbTo]

That worked great. Thank you very much!