In a Google sheet you can easily create a dropdown list based on a range of cells using menu Data => Data validation. I now need two dropdowns that have a dependency: the first one lists programs, the second one lists projects of the selected program. Here is how it currently looks:

How can I add a filter to the range to show only projects of the selected program?

Instead of a range I can specify a formula in the data validation dialog box. I tried custom formula =QUERY(D2:E12, "Select E where D = """&B2&""" "), but that does not create a dropdown.

Short test data, the actual table has 700 rows: