Two different lists with the same name range for dependent drop-down lists

Hello everyone,

The excel sheet I'm working on consists of dependent drop-down lists, category, subcategory and resolution. Now let's say we choose "Inquiry" in category list and "Policy" in the subcategory, the "Inquiry > Policy" related options will appear in the following column of resolution.
Now we have "Inquiry > Policy" path and also "Complaint > Policy" path which have different related options but we need to name both lists with different options, "Policy", so that the INDIRECT function will recognize it. How do I do that?
Thank you.

You require two different lists as you know because of different content. They however cannot have the same name. I suggest that the Inquiry Policy be IPolicy and the Complaint Policy be CPolicy.

Here is a very simple example in a workbook how this could be accomplished. This takes the first letter of either Complaint or Inquiry and combines it with the word Policy which in my example is in a cell...it could instead by hard coded in the INDIRECT formula.

By the way, welcome to the forum.

If the above isn't sufficient to help you, please upload the workbook that you are working on.

Here are the instructions to follow:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

This example of your workbook should show you what you need to know to proceed. You don't have all the lists that you require so I only went a little way into it.

To prevent a change to a drop down choice before other choices that have already been made, use a formula like this in the data validation list formula. This example formula applies to your A2 on Sheet1.

In Data Validaton, Allow List, in the formula field enter:

Formula:

Please Login or Register  to view this content.

This means that if B2 is empty, allow the name CallType if B2 is not empty try to use BlockList. BlockList doesn't exist so no Drop down list is presented. If you implement this for all the drop down lists, every cell that is dependent upon the choice that you want to change will have to be deleted before a new choice can be made.

This has been implemented in cells A2 and B2 of Sheet1. To change A2, B2 must be empty, to change B2, C2 must be empty.

I have also added a section that uses a VLOOKUP which you might want to consider. It takes all the terms that you have in your lists and titles and converts them all to legal names if they contain illegal characters.

dsand621, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.