Changing data validation list range by cell value

Question

Hi,

I want to change the data validation list depending on the value of a cell in previous column. E.g. as follows:

There are 4 defined lists (ranges):

"DEPARTMENTS" : ["ADMIN","MARKETING","SALES"]

"ADMIN" : ["Frank","Jack"]

"MARKETING" : ["Charlie","John"]

"SALES" : ["Eddie","Paul"]

In column B there is a data validation rule to allow items from "DEPARTMENTS"

In column C I want to have a data validation rule, that depends on the value in column B e.g. if B2 value is "ADMIN", then the dropdown list in C2 should display "Frank" and "Jack", in case B2 value is "SALES", it should display "Eddie" and "Paul".

I tried using =INDIRECT("$B$"&ROW()), but the dropdown shows only "ADMIN" or "SALES".

How can I get the dropdown list contents to match with the values of the named ranges?

Answer 1

Hi Juha_666

Greetings! I am Vijay, an Independent Advisor. You are trying to create a dependent drop down. I have created an article for this on my blog @ http://eforexcel.com/wp/article-14-dependent-da...

You need to use =INDIRECT($B2)

Using the steps above, I have created the file for you. Download from https://1drv.ms/x/s!Akd5y6ruJhvhpmzXPOpIn1he33M...

Answer 2

Thank You very much for fast reply!

This is exactly what I need.

WBR,

Juha