Depedent Data Validation

[Prashantvk123]

Hi all,

Hope you ared doing well.

Please refer attached excel.
I am trying to get the dependent data validation in the attached excel.
this tracker is basically used for audit purpose and we are tracking associates accuracy in this tracker.
The data for validation is in cell P2:s46 there are monthly, daily and weekly activities and they are having different CTQs.
I want to add a data validation for e.g. if I select “frequency” in cell D10 as “Monthly” then in cell E10 (“Activity name”) it should give me only those “Activities” which are “Monthly”. Next when I select particular “Activity” in cell E10 it should give me “CTQs” assigned only for that particular “Activity” in cell F10. Need a macro free solution

Could you please assist??

 

[jeffreyweir]

If all your activity descriptions are unique, you can use the approach set out at either http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/ or http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/ (just ignore the macro component on that last link...all the macro does is clear any 'stranded' downstream choices if upstream choices change, and is not essential)

This is a bit tricky to set up.

Otherwise I have another solution that uses some PivotTables. I'm in the process of coding up a 'Dependent Data Validation' wizard for the Excel book I'm writing, and so might be able to provide you with a working beta in a couple of days.

 

[bobhc]

@jeffreyweir is very very busy writing his book and and such is spending as much time as possible on it......

So you may find this link of great use.

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


.

 

[jeffreyweir]

Note that the approach at that link requires you to manually set up named ranges, and that all categories & sub categories are unique. My approach doesn't.

 

[AVK]

For better result plz mentioned output result in respective cell.
I have attach file. plz go through.

 

[jeffreyweir]

@atul khandekar: The problem with that approach is that every item then appears in the dropdown list. If there are hundreds or thousands of items, this makes using dropdowns a pain. Using cascading dropdowns removes that pain.

 

[Nebu]

Hi

I have applied the logic for your Frequency and activity column, follow the logic and apply for rest of the drop downs , the key here is use of name ranges and choose function see the attached.

Thanks
Nebu

 

[AVK]

Hi

I have applied the logic for your Frequency and activity column, follow the logic and apply for rest of the drop downs , the key here is use of name ranges and choose function see the attached.

Thanks
Nebu

Yes agree. I think offset option also suitable.

 

[Prashantvk123]

@jeffreyweir your post is awesome.
I hve used your sample excel file to build the file I need..... it works perfectly...
Thanks a lot