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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With