Multiple Colums Data Validation

[11curiosity11]

Hi,

I plan to make a sheet for data capturing. Sheet 2 will be my base sheet with all the data in it, and sheet 1 will have blank fields to be filled in via drop downs.

Suppose the heading in Sheet1 are Products(Column A), Brands(Column B), Shades(Column C).

Now assume I select a product in A1[from the drop down], the selection is B1 will be depending upon my selection in A1. Now my question here is how can I create a Data Validation for C1 depending only upon my selection on A1.

eg. from the drop down I select in A1 to be Shoes, in B2 because of the drop down validations I get the brands Nike, Reebok, Adidas. I select any one of them. In C3 I want Shade to come as drop down but based on my selection in A1 and not influenced by any-other thing.

Please Help!

 

[Marcelo Branco]

Hi,

Maybe this can help
http://www.contextures.com/xldataval02.html

M.

 

[11curiosity11]

Hi Marcelo,

I have already referred to that link, but it only helps me to set data validations for the second column. I am not able to set data validation for the third column. This third column is only based on the selections in the first column and is not influenced by my selection in the second column.

 

[Marcelo Branco]

Ok, now i understood your problem. You need two lists based on A1: the first to list the brands based in Shoes in A1; the second to list the Shades also based in Shoes in A1. Am i right?

M.

 

[texasaswang]

Opps. nevermind.

 

[11curiosity11]

Yes exactly Marcelo.

 

[Marcelo Branco]

Maybe if you create lists like this (taking your example for Shoes) and naming them as below

ShoesShades
A
B
C
...

CarsShades
D
E
F
...

and using in DataValidation of C2
List
=INDIRECT(A2&"Shades")

M.

 

[11curiosity11]

That worked! Thanks Marcelo. A question that arose is then why do people use the offset function in the first place to create a data validation?

 

[Marcelo Branco]

You are welcome!

About your question: i dont know, maybe because its simpler...

M.

 

[11curiosity11]

Anyways Thanks again Marcelo!