Data Validation based on Cell Value

  • Thread starter Michael Hudston
  • Start date
M

Michael Hudston

I would like to set a validation list on a column based on the value of the
adjacent cell in the other column, but can not seem to figure out how to do
this.

I have the following named ranges.

Category Which contains :- Capacitor, Resistor, Diode, Connection and others.
Capacitor Which Contains :- Air Trimmer, Variable, Fixed, Surface Mount Etc
Resisitor Which Contains : Variable, Wire Wound Etc

I also have named ranges for Diodes, Connections and other items that appear
in the Category List.

What I am trying to achieve is

Depending on the Value I select for Column A from the Category List, then I
want the Adjacent Cell in Column B to validate against the relevant list.

Ie if in Column A i select a category of Capacitor, then the Validation in
the adjacent cell should be limited to the list in named range "Capacitor"

Where do I even start?

Help Please.
 
J

Jackpot

In cell B1; set data validation>List and in source type..This will populate
the list with the named range mentioned in cell A1.

=INDIRECT(A1)

PS: when you apply this in cell B1 ; there will be a confirmation message if
A1 is blank. Please ignore and proceed.
 
S

steve

Create a named range with the list of your categories as well and call it
category.
In cell A1 use the data validation choose "allow" >list< and in "source"
enter "=category"
You can copy/fill cell A1 down and the validation will be copied down as
well.
In cell B1 use the data validation option "allow" >list< and in "source"
enter "=indirect(A1)"
Again copy/fill cell B1 down.
This should build up a table so that when you select a catagory in a cell in
column A, should then allow you to pick an item in column B.
It may be worth adding a blank cell at the start of your named ranges so
that when you do fill the cells down they will automatically contain the
first item from the named range (blank)

Regards
Steve
 
S

steve

Note to self "READ POSTS PROPERLY"
Sorry you had already said that you had created the named range Category
along with the others.
But I hope the rest of the answer is useful.

Regards
Steve
 
R

Roger Govier

Hi Michael

Take a look at
http://www.contextures.com/xlDataVal15.html

--

Regards
Roger Govier

Michael Hudston said:
I would like to set a validation list on a column based on the value of
the
adjacent cell in the other column, but can not seem to figure out how to
do
this.

I have the following named ranges.

Category Which contains :- Capacitor, Resistor, Diode, Connection and
others.
Capacitor Which Contains :- Air Trimmer, Variable, Fixed, Surface Mount
Etc
Resisitor Which Contains : Variable, Wire Wound Etc

I also have named ranges for Diodes, Connections and other items that
appear
in the Category List.

What I am trying to achieve is

Depending on the Value I select for Column A from the Category List, then
I
want the Adjacent Cell in Column B to validate against the relevant list.

Ie if in Column A i select a category of Capacitor, then the Validation in
the adjacent cell should be limited to the list in named range "Capacitor"

Where do I even start?

Help Please.

__________ Information from ESET Smart Security, version of virus
signature database 5172 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5172 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top