I am trying to create a way for people to select 4 fields to identify a complaint that was made on a product.
Column A has Causes. When you select your Cause, you then select Column B High Level Failure Code. Then to Column C for Failure Code Detail, lastly Column D for Failure Monitoring Code.
The issue is choices in Columns A-C could have multiple options. I need the pick list to only show unique values based on their choices. The only way I can think of doing this is painstakingly creating a named range for every selection. I am really hoping there is an easier way to do this. I have attached a copy of the sheet.
Hello,
Depending of what version of Excel you have, below are examples of how to set up dynamic data validation lists.
Excel 365
Dynamic Dependent Data Validation • My Online Training Hub
Older versions
Excel Data Validation With Dependent Lists • My Online Training Hub
This requires some work to set it up, but it is a one time job as long the data doesn't grow, but I fear that it does in your case. Give it a try.
Br,
Anders
Hi Anders,
Thank you for your response. But it unfortunately did not solve my issue. As I need to go down to 4 columns, and each column has duplicate values, I cannot get it to filter for just those based on my selections. I have attached my file for your review.
In Column A for example, there is Breakage. Then Column B is User Mishandling. But when I get to column C there are 4 choices. Then I still have to select column D after that.
Filtering Column A for "Physical Damage. Not enough information to attribute to the user" brings up the same issue. Multiple choices are not unique in the filter or are not available for selection.
Hello,
I never said it would be easy to set it up, but see attached file for an example.
You can add more data to the table and it will pick up the new values dynamically. This solution is only based on data from previous column, that is you get the value list for FC_Monitor based on what you picked in FC_Details, not at what you picked for values in Cause, FC_High and FC_Details. If it is that you want then I don't know if this solution will suffice, then I suppose you need VBA to help you out.
Br,
Anders
Hello again,
While trying to get to sleep I was actually able to think of a way to only get those values that belongs to the picked Cause, FC_High and so forth. Was also able to simplify the formulas, so sometimes it is good having hard time to get to sleep. See if version 2 of the example file is more in line to what you want to get.
Br,
Anders
Thank you so very much for your help! This is exactly what I needed. Very much appreciated. Sorry you lost sleep 🙂
Hello,
Great that it works as you wanted.
Br,
Anders
