Expertise Advice required on Using Vlookup and Data Validation for dropdown lists

[Abu4rmDXB]

Dear Excelians,

Kindly find the attached file where I have shown my work and got stuck up. From the database sheet you can see that there are failures, causes for the failures and resolution to those those failures with respective codes.

My task is to;

1. Pick a machine (for example, any one from machine #01 - machine #10),
2. Select a failure description from the drop-down list under 'failure description' column,
3. Automatically the failure code should appear on the failure code column,
4. According to the failure description or the code, the cause related causes should be shown in the 'cause description' column's drop-down list,
5. Automatically respective resolution and resolution code must appear in their respective columns once the cause is picked.

I have used Data Validation and Vlookup for this process. You may use the name manager to check the name ranges I have assigned for you reference.

At this moment, the first row works fine but from the second row on-wards the cause description drop-down list doesn't gets updated even after pressing F9 (refresh).

Kindly help me out in this task.

Your help will be very much highly appreciated.

Thanks & Best Regards,
Abdul Rahman

 

[Khalid NGO]

Hi Abdul Rahman,

I think you just need to change absolute reference in your data validation formula as:
=INDIRECT($C2)

Regards,

 

[Abu4rmDXB]

Hi Khalid,

changing "=INDIRECT($C$2) to =INDIRECT($C2) doesn't work.

For example, when the data in A3 is changed to "air leak", only the failure code changes but not on the rest of the columns data changes.

Please do the needful in fixing this error.

Thanks a lot.

 

[Khalid NGO]

Hi Khalid,

changing "=INDIRECT($C$2) to =INDIRECT($C2) doesn't work.

For example, when the data in A3 is changed to "air leak", only the failure code changes but not on the rest of the columns data changes.

Please do the needful in fixing this error.

Thanks a lot.

Actually, first i did cleared all the contents of range (D2:D11) by pressing delete key, then applied DV formula =INDIRECT($C2) on the same range, and it showed the correct DV list.

Now if again you change the B2 from "Air Leak" to any other option, you need to re-select the new available options in D2 Data Validation.

 

[Abu4rmDXB]

Hi Khalid,

I would like to have the column D's drop-down list (cause description) to automatically change by itself as per the column C's (Failure code) data because I'm trying to make our work easier rather than performing DV for each cell in column D.

Kindly do the needful.

Thanks.

 

[Khalid NGO]

Hi Khalid,

I would like to have the column D's drop-down list (cause description) to automatically change by itself as per the column C's (Failure code) data because I'm trying to make our work easier rather than performing DV for each cell in column D.

Kindly do the needful.

Thanks.

Hi Abdul Rahman,
Jeff had written 2 posts regarding your requirement, see if you get some help:

http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

Regards,