Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel Data Validation List using formula with filtering

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.

like image 546
Stipo Avatar asked Apr 25 '13 15:04

Stipo


People also ask

How do I filter Data in Excel with a drop-down list?

Select the cells that you want to contain the lists. On the ribbon, click DATA > Data Validation. In the dialog, set Allow to List. Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.

Can you use filter function in Data Validation in Excel?

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List. If you are working in Excel 365, you can filter data using the FILTER function.

Can you put a formula in a Data Validation list?

On the Settings tab of the Data Validation dialog window, select Custom in the Allow box, and enter your data validation formula in the Formula box. Click OK.


1 Answers

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.

like image 57
Alan Waage Avatar answered Sep 20 '22 14:09

Alan Waage