2

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.

Data sheet table

Drop-down validation sheet

0

1 Answer 1

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:

enter image description here

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

enter image description here

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):

enter image description here

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

enter image description here

You can test it out here

2
  • Alright. I have tried it out and it worked. How can I implement this for multiple sheets containing this activity drop-down data validation?
    – Ganiyat
    Commented Jul 31, 2023 at 14:42
  • Since there's no in-built function for this, you should repeat the process in each sheet
    – Martín
    Commented Jul 31, 2023 at 15:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.