Excel Data Validation List using formula with filtering

Question

I have following workbook:

Worksheet Accounts:

Worksheet Posts:

I would like to know if it is possible to define Data Validation of type List to entire column B in sheet Posts using formula, so that Data Validation popup shows only Account Ids from Accounts sheet which Website column matches Website column of a selected row in Posts sheet and which have value Active in Status column?

In SQL-ish or LINQ-ish words:

SELECT Id FROM Accounts WHERE Website = @SelectedPostRow.Website AND Status = Active

Marks on the second image shows which values should be shown in a drop-down.

Answer 1

Yes you can do this, but it requires some supporting setup.

First for each of your website options, you need to create a named range for the options that would be in the drop down you are seeking to create.

To do this, just highlight the list of cells and right click, the choose Name a Range

Then you need to create a lookup list for your website names to the named range possiblities

Then in your data validation source use a forumla like this:

=indirect(vlookup(a1,$i$8:$j$13,2,false))

then whala, the dropdown list changes based upon the website value.

Now if you are also needing to automate the named range bits, you could change them to encompass entire columns and then use a pivot table to pull in the data. Would just need an independant pivot for each website option.

Each time you pulled in fresh data you would need to refresh the pivots, but it would function.