cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
winwell
Frequent Visitor

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

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

2 REPLIES 2
RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

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 🙂

 

,

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (58,556)