CODEWITHSUNDEEP
vbaexcelvalidationcheckbox
Jethro H
Jethro H

Reputation: 37

CheckBox to limit a data validation list

Moving forward, slowly.......

I added a column, that with the combination of my checkboxes, gives the name of active employees.

Screen Shot of active list of employees

Screen Shot of active list of employees

If I use $T$6:$T$16 as my validation list, I still get 11 options in the drop down. I want the drop down to show only the 5 names that are in that list.

Upvotes: 0

Views: 482

Answers (1)

Wolfie
Wolfie

Reputation: 30101

No need to use VBA here, Here's a quick solution.

Use a 1 or 0 in a column next to the employees' names corresponding to whether they are active or not.

Then create a column in the sales sheet which has a vlookup for this value

=VLOOKUP(E4,$A$4:$B$8,2,FALSE)

This looks for value E4 (the name), in range of all names, second column for their active status, FALSE for an exact match.

Then use a filter:

  • Highlight range of sales
  • "Data" tab
  • "Filter"

Then you can use the filter on the new Active column to only show the employees which are still active. See the linked image for the final outcome. The vlookup lives in column D.

Final outcome image in Excel

enter image description here

Of course if you want this in two sheets, you can split what I've done to different sheets with no extra effort other than adding sheet references to the range in the vlookup.

Hope this helps

Upvotes: 1

Related Questions