Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation based on Cell Value
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation based on Cell Value
In cell B1; set data validationList 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. "Michael Hudston" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation based on Cell Value
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 "Michael Hudston" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation based on Cell Value
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 "steve" wrote in message ... 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 "Michael Hudston" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation based on Cell Value
Hi Michael
Take a look at http://www.contextures.com/xlDataVal15.html -- Regards Roger Govier "Michael Hudston" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation based on cell value | Excel Programming | |||
Hide/Show Rows based on Cell Value with Data Validation | Excel Programming | |||
Creating a Data Validation List based on a Value in another cell | Excel Worksheet Functions | |||
data validation to restrict input in cell based on value of cell above that cell | Excel Programming | |||
Sorting data based on validation cell | Excel Programming |