Search code examples
exceldropdown

How to create dropdown from existing data?

One of my worksheets (Sites) contains a list of sites.

Column B contains the sites unique ID, Column C contains the site type. This is an example, the real data will have many more entries. Eg:

Column B     Column C
1234-56789   NW175
5468-58754   SW124
8787-97979   PO962
9247-75204   NW116
1458-56155   NW120
7486-89251   KA960
975-983044   PO630

I'd like to be able to present a dropdown list in cell A5 on worksheet 1 (Home) containing only entries starting with NW.

I'd like the list to appear as:

NW175 - 1234-56789   
NW116 - 9247-75204   
NW120 - 1458-56155

Is there any way to do this without using macros?

Solution

  • If you are using Excel 365 then you can use the new FILTER-Function. Also I would propose that you use a table to define your site-configuration.

    There you add a new column, e.g. Full name and apply the formula to concatenate both values.

    Then the new FILTER-function comes into play - see my screenshot - you have to use the formula =FILTER(tblSiteInfo[Full name];LEFT(tblSiteInfo[Site Type];2) = "NW") --> the filtered result spills down as a new list

    Next step: define a name for the cell (and only the cell with the formula) and apply a # at the end of the name-address.

    You can then use this name for your validation list.

    enter image description here

    Benefit of this solution: if you add more sites to the configuration table the validation list will update automatically.