How to populate drop down with distinct values in excel Column A based upon value in Column B

[winwell]

Hello 

I have a table called skill_scores_table with several columns. It is iin Excel in OneDrive.

Two of the columns are FULLNAME and MANAGER. FULLNAME is used to capture the name of members of staff MANAGER is for their manager's name. So each manager will have several staff. Here's a simple example

FULLNAME | MANAGER

John | Manager A

Mary | Manager B

David | Manager A

Steve | Manager B

Joanne | Manager A

Tim | Manager A

I want to populate a drop down with the staff that are applicable to the manager who is currently logged in. I can get thier name using the User() function. So if the current user was Manager A , I want the drop down to only show John, David, Joanne and Tim.

Whats the best way to achieve this?

I've tried a few variations of Filter() and Distinct() but it doesn't work. I tried this in the Items property of the Drop Down:

Filter(skill_scores_table .FULLNAME, MANAGER=User().Fullname) - nothing returned

Distinct(skill_scores_table , FULLNAME) - nothing returned with a drop down but tried this with a listbox and it did populatge so what's different about a drop down?

I also thought I could create a new collection containing the FULLNAME of the filtered table MANAGER column but I can't figure out how to do that either.

I just need a nudge in the right direction so hopefully somebody might be able to help me out?

Thanks

[RandyHayes · Accepted solution]

@winwell 

Try this for a nudge...Set your Items property on your dropdown to this:

   Filter(skill_scores_table, Manager=User().FullName)

Make sure your DropDown has its Value to display set to "FULLNAME".

Also, to be nice and sort...

  SortByColumns(Filter(skill_scores_table, MANAGER=User().FullName), "FULLNAME", Ascending)

Hope this is the nudge you're looking for.

[winwell]

Thanks very much - that **bleep** Value field!! Forgot all about it!!

I used this 

Distinct(Filter(skill_scores_table,MANAGER=txtManagerName.Text),FULLNAME)

which correctly displays the staff names as expected.  (There is another PERIOD column so there could be multiple same names for one manager so I needed it to be distinct.

I then added your suggestion like this:

Distinct(SortByColumns(Filter(skill_scores_table,MANAGER=txtManagerName.Text),"FULLNAME",Ascending),FULLNAME)

which now shows the distinct staff names sorted in ascending order.

Thank you for the nudge - it was jsut what I needed 🙂

,