Google sheet: How can i create a drop-down data validation in a column whose list changes depending on the value selected in the previous column? [duplicate]

Question

I have a data sheet containing two columns (Core Indicators and Activities) with core indicators and their corresponding activities. Each Core indicator has about 4 or 5 unique activities. On a different sheet, I want to create drop-down data validation for Both the core indicator and the activity.

I created the one for Core indicators using the Drop-down (from a range) option in Data validation. For the Activity column, I want to be able to create a drop-down data validation of activities. The list in the drop-down should be based on the core indicator selected in the first column as each indicator has its corresponding activities.

Answer 1

One way to create conditional data validation is with auxiliary columns, in the same sheet or in other auxiliary sheet.

You can write in the same row:

=IF(A2="","",FILTER(Sheet1!B:B,Sheet!A:A=A2))

And drag it down

Or use REDUCE to make it an array formula, but it can be a little too heavy if you have lots of rows. In row 1 you can use this formula:

=IFERROR(REDUCE(A1,A2:A,LAMBDA(a,ind,VSTACK(a,IF(ind="","",TRANSPOSE(FILTER(Sheet1!B:B,Sheet1!A:A=ind)))))))

In both cases, the result will look like this:

Now you create the data validation for the whole range, but only taking the first row:

In case it doesn't automatically work, you should enter again the data validation rule and delete the "$" that are there at the right in order to keep it again as F2:2 (or from the column you start):

Then, the data validations will depend on the row they're in, and the values will be filtered row by row:

You can test it out here