Excel Data Validation List using formula with filtering

Question

I have following workbook:

Worksheet Accounts:

Accounts worksheet

Worksheet Posts:

Posts worksheet

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.

Alan Waage · Accepted Answer · 2013-04-25 18:53:45Z

3

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.

answered Apr 25, 2013 at 18:53

Alan Waage

6344 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stipo Over a year ago

If I understood you correctly, I must create a named range of Account Ids for each Website value. This won't work for me since Accounts sheet can be additionally populated with new accounts and I do not want to increase named range every time this happens. Pivot also wouldn't work because it would require refreshing. I want this to be completely automated with no additional bookkeeping tasks involved. And what about Status = Active part?

Alan Waage Over a year ago

The pivot table would be what you need to do the Status = Active piece. Also Pivots can be refreshed via VBA, which can be automated.

Israel · Accepted Answer · 2015-04-29 17:01:39Z

3

This problem need a bit of a preparation. In the same sheet, or in another, copy your data (or add the relevant cells)

In column A you have a number that could be ranked. I obtained it with (cell A2):

=IF(C2=$J$2,1,0)*IF(E2="Active",1,0)*ROW()

In column B rank the number and exclude the unwanted rows (B2):

=IF(A2=0,0,RANK(A2,A:A))

Then you can VLOOKUP in column H, using a enum in column G (manually entered). The formula for H1:

=IFERROR(VLOOKUP(G2,$B$2:$D$9,3,FALSE),"")

Now you could set your validation based on column H

screenshot

PS: there could be small errors in the formulas as I have translated them from italian and I cannot test in english.

edited Apr 29, 2015 at 17:01

Israel

1,2342 gold badges14 silver badges26 bronze badges

answered May 9, 2013 at 12:40

momobo

1,8001 gold badge14 silver badges20 bronze badges

Collectives™ on Stack Overflow

Excel Data Validation List using formula with filtering

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related