Data Validation - How to work with 2 Dynamic Lists where One list have Repetitive values.

Dear all,

Regarding Data Validation in Excel (2010).

Need your help in Data Validation to work with 2 Dynamic Lists where One list have Repetitive values.

= In the attached file, you will see =
" GradesNames " sheet has List of Grades(repetitive values) under " Grades " column; and List of Names(unique values) under " Names " column. Over the time, these two lists will increase in length.

" GradesNamesList " sheet should have Grade List at B1 and Name List at B2.

Appreciate any help on the same.

Thanks,
Vinod Krishna

Hello Jaslake,

Thank you for looking into this

The data I would be working on, will have repetitive values. Like in Grades column of the attached sheet.

Appreciate any help on this.

Many thanks,
Vinod Krishna

Hi Jaslake,

Appreciate your attempt though

Please see the attached. I have modified the inputs in sheet " GradeNames ".

Here's my approach in solving this:

* Organize the inputs in a different manner. (Actually not affordable). See the sheet " GradeNames " under columns E through H.

* Group the Range E1 to H1 and name it as GradeList.
* Group the values A,B and name that range as 'One'
* Group the values C,D and name that range as 'Two'
* Group the value E and name that range as 'Three'
* Group the value F and name that range as 'Four'

Switch to sheet " GradeNamesList " and write the Data Validation logic in cells B1 and B2 as follows:
B1 -> =GradeList
B2 -> INDIRECT(B1)

This is exactly what I'm looking for and it works Great for fixed data

Challenges:
* No organizing the data as seen in sheet " GradeNames " under columns E through H.
* What if the list grows in size? Grouping and Naming Range every now and then is not an efficient way and also it is manual work.
* When I change the Grade List to see the list of Names associated with that Grade, the value chosen in the NameList should get deleted.

@All,
Please help in improving this logic further.

Thanks,
Vinod Krishna