Change drop down list in one cell based on values in other cells
 

Hi, Can someone pls help. I want to update value in a drop down list based on
selection in preceeding drop down list e.g. there is a list of 10 business
units & within each business units there 15 to 20 sub business units. So if
business unit 'BU1' is selected in drop drop list in say cell A1 then on cell
A2 the drop down list should be populated with the corresponding sub-business
units of 'BU1'. Similarily if 'BU2' is selected in drop down list on cell A1
then on cell A2 the drop down list of corresponding sub-business units of
'BU2'. Hope it makes sense.
Cheers,
Hemi

Answer: Change drop down list in one cell based on values in other cells
 

Hi Hemi,

Yes, it definitely makes sense. You can achieve this by using the "INDIRECT" function in Excel. Here are the steps to do it:

1. Create a table with all the business units and their corresponding sub-business units. Make sure to give this table a name (e.g. "BusinessUnits").
2. Select cell A2 and go to "Data Validation" under the "Data" tab.
3. In the "Data Validation" dialog box, select "List" under "Allow".
4. In the "Source" field, enter the following formula:
   Formula:

   ---

   "=INDIRECT(A1)" 


   ---

   .
5. Click "OK" to close the dialog box.
6. Now, when you select a business unit in cell A1, the drop-down list in cell A2 will automatically update with the corresponding sub-business units.

Note: Make sure that the names of the business units in the drop-down list in cell A1 match the names in the "BusinessUnits" table exactly.

Change drop down list in one cell based on values in other cells
 

Check out the below link by Debra..on dependent dropdown lists

http://www.contextures.com/xlDataVal13.html

If this post helps click Yes
---------------
Jacob Skaria


"Hemi" wrote:

Hi, Can someone pls help. I want to update value in a drop down list based on
selection in preceeding drop down list e.g. there is a list of 10 business
units & within each business units there 15 to 20 sub business units. So if
business unit 'BU1' is selected in drop drop list in say cell A1 then on cell
A2 the drop down list should be populated with the corresponding sub-business
units of 'BU1'. Similarily if 'BU2' is selected in drop down list on cell A1
then on cell A2 the drop down list of corresponding sub-business units of
'BU2'. Hope it makes sense.
Cheers,
Hemi

Change drop down list in one cell based on values in other cel
 

Thanks Jacob for your help.

"Jacob Skaria" wrote:

Check out the below link by Debra..on dependent dropdown lists

http://www.contextures.com/xlDataVal13.html

If this post helps click Yes
---------------
Jacob Skaria


"Hemi" wrote:

Hi, Can someone pls help. I want to update value in a drop down list based on
selection in preceeding drop down list e.g. there is a list of 10 business
units & within each business units there 15 to 20 sub business units. So if
business unit 'BU1' is selected in drop drop list in say cell A1 then on cell
A2 the drop down list should be populated with the corresponding sub-business
units of 'BU1'. Similarily if 'BU2' is selected in drop down list on cell A1
then on cell A2 the drop down list of corresponding sub-business units of
'BU2'. Hope it makes sense.
Cheers,
Hemi