data validation rule? For Google Sheets.

[jesskidn]

Is there a way to create a rule on the data validation so that based on the the option chosen from the dropdown in the "division" column, only one option is available to choose in the "division code" column?

 

[James006]

Hi,
You request is pretty much standard ... dependent drop-down lists
e.g. see :

Dependent Drop-Down Lists

Currently have a series of dependent drop lists using Tables and indirect formulas using the below formulas. It works perfectly but I would like to find a way to maintain a single table as per the below versus have to create multiple tables to ease with data modification such as, (1) new variant...

www.mrexcel.com

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you only want one a single option for the Division code, why not use a formula rather than data validation?

 

[jdellasala]

I suggest using Excel Tables which will make things a lot easier:

Book1
	A	B	C	D	E
1	Table1			Divisions
2	Division	Code		Divisions	Code
3	New Jersey	NJM		New York	NYM
4	Deleware	DEM		New Jersey	NJM
5	New Jersey	NJM		Deleware	DEM
6
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
B3:B11	B3	=XLOOKUP([@Division],Divisions[Divisions],Divisions[Code],"")

Cells with Data Validation
Cell	Allow	Criteria
A3:A11	List	=$D$3:$D$5

As Table1 grows, the Data Validation list will be replicated in new rows, as will the XLOOKUP formula. As the Divisions table grows, so will the Data Validation list. All automatically.

 

[jesskidn]

thank you all for the replies!

NOTE: I am using google sheets, and i have updated my profile "about me section" as that isnt an option to choose in the list provided.

 

[jdellasala]

thank you all for the replies!

NOTE: I am using google sheets, and i have updated my profile "about me section" as that isnt an option to choose in the list provided.

I have very little experience with Sheets, but I imported the tables above, and the Data Validation and XLOOKUP both translated. Unfortunately Sheets doesn't appear (at first glance) to provide the utility of an Excel Table. That is the things that happen automatically with Excel Tables doesn't happen automatically with Sheets. However, if your source Data Validation list is static, and you have an idea of how much data (how many rows) you'll need, it looks like it could be set up well.

 

[Fluff]

thank you all for the replies!

Any chance you could answer my question?

Also questions regarding Sheets should be posted in the General Discussion & Other Applications section of the board & you need to make it clear which App you are using.
I have done that for you this time.

 

[jesskidn]

I'd be happy to use a formula, I was already using data validation (which is when i ran into the issue of needing a dependent list). Do you have a suggestion of an easier / more efficient way to do this?

 

[jdellasala]

I'd be happy to use a formula, I was already using data validation (which is when i ran into the issue of needing a dependent list). Do you have a suggestion of an easier / more efficient way to do this?

Not using Sheets.

 

[Fluff]

You can create a "lookup table" & then use Vlookup (or similar) to get the correct value.