Data Validation - Automating. Facing an issue

[Vinod Krishna.C]

Dear all,

I'm currently trying to automate the process of creating Data Validation.

PFA.

I have Grades and Names List.

" GradesList " Range has values "One, Two, Three, Four "

I'm trying to map Names List to GradesList using INDIRECT().

Debug the function DataValidation() to understand the issue better.

I'm having a issues in the below code:

Please Login or Register  to view this content.

Please help.

Thanks,
Vinod Krishna

[:) Sixthsense :)]

In D2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$7),0)),"")

In E2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$B$10,ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1),ROW(1:1))),"")

Drag both the formula's until row 7

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

In B10 DV List

=OFFSET($D$2,,,SUMPRODUCT(1*($D$2:$D$7<>"")))

In B11 DV List

=OFFSET($E$2,,,SUMPRODUCT(1*($E$2:$E$7<>"")))

Refer the attached file for details

[Vinod Krishna.C]

Dear SixthSense,

Appreciate your help with a Solution using Formulas.

Forgot to mention that my data will always be dynamic and I would love do this via Macro as in my 1st post.

Kindly let me know if this would be possible.

Thanks,
Vinod Krishna

[:) Sixthsense :)]

Copy the below code and do right click on sheet tab and select view code and paste it.

Close the VBA window (Alt+Q to close VBA window) and return to that sheet and check the dropdown cells.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see attached file.

[Vinod Krishna.C]

Dear Sixthsense,

Brilliant Coding Appreciate all your efforts.

I tried to relate the above code with the real scenario in hand.

I have named the Ranges and kept it ready.

Challenging Situation:-
Concerned data is spread across 3 sheets.

PFA to understand my situation.

I added little code. Finding it difficult to make all the necessary changes.

Hope you can help

Regards,
Vinod Krishna

[Vinod Krishna.C]

Pl see attached file.

Hi Srinivas,

Your solution seem simple. Appreciate it.

PFA for the real scenario in hand.

Challenging Situation:-
* Concerned data is spread across 3 sheets.
* Even Data Validation needs to done by code not manually

Thanks,
Vinod Krishna

[kvsrinivasamurthy]

Things are not clear and the file attached does not contain any data. Give sample of details of data as available in your file along with the expected result.