How to be more concise in using data validation in excel 2016

Question

Can someone help me regarding this issue?

Here's the scenario:

I have 2 columns: A and B

A will show the main category and B should show the sub category.

What I want to happen is this:

When I select a category in cell A, the sub category in cell B should be narrowed down based on the value I selected in cell B

here's the exact options:

Column A: Agent Driven Process Driven Products/ Services

Column B: The selection should change depending on what option I selected in Column A

For Agent Driven

Setting right expectation Communication Incomplete/ incorrect resolution Inconsistent information Professionalism

For Process Driven

Took too long to solve the issue Returns/ refunds Took too long to respond

For Products/ Services

Product Quality Missing Shipment Incorrect/ damaged product received Issue with our website Replacement/ item is OOS Incorrect/ damaged replacement received

Answer 1

What you describe is called dependent data validation. It can be done with named ranges and the Indirect function in the source of the dependent validation. Debrah Dahlgliesh has a step by step tutorial for this at Contextures.com

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

Answer 2

If by narrow down you mean combo box to select from , you can do that using data validation-list. Then you need a vba code to change the list source, based on value from Column A