Excel Data Validation using Multiple Columns

Question

Hi

I have data in a worksheet like so:

Name        Skill1                           Skill2                           Skill3

Bob           Computers                  Fishing                        TV

Dave         Fishing                         TV                               Cars

John          TV                                Computers                 Chess

Mike         Cars                              Computers                  TV

I'd like to have a list of skills (the same ones that appear in the array above):

Computers

TV

Fishing

.

.

and beside each one have a Data Validation drop down that will show all the names that have that as one of their skills.  EG for Fishing the drop down will contain Bob and Dave, for Computers the drop down contains Bob, John and Mike

Any pointers, ideas etc gratefully received

TIA

Answer 1

Hi Ashish

I have not checked out your proposed answer yet, and will do so tonight.

The actual data will contains dozens of names, so to list everyone would be cumbersome.  This is for working out rosters so want to minimise the chance of mis-entry., and make it easy to fill the data in.

Cheers

Answer 2

Hi,

Are you absolutely particular about there being a validation drop down?  In other words, would you be willing to work with a solution where Bob would appear as many times are skills that he possesses.

If you can live with what I suggested, you may use my solution at the following link - http://www.ashishmathur.com/converting-a-matrix-data-layout-to-a-tabular-layout/

Hope this helps.

Answer 3

Hi, use data validation dependent lists, see Debra explanation and examples

http://www.contextures.com/xlDataVal02.html