Forum Discussion
Connected drop-down list
Hi guys,
I have a question. Below is the data set i have on Sheet 1.
On Sheet 2 i want Have 2 columns - Choose Category (Column A) and Choose Order Item (Column B)
I turned Column A into a drop down list using data from Sheet 1 (Category).
What i want to do is if I choose LAB from the drop-down list - I want the drop-down list in Choose Order Item (Column B) to display on Lab A, Lab B, Lab C.
If in Column A, I Choose LAB - Column B displays Lab A, Lab B, Lab C.
If in Column A, I Choose MED - Column B displays Med D, Med E, Med F.
If in Column A, I Choose OTHERS - Column B displays Others G, Others H.
Is it possible?
Thank you
| Category | Order Item | Unit Price |
| LAB | Lab A | 1 |
| LAB | Lab B | 1 |
| LAB | Lab C | 1 |
| MED | Med D | 1 |
| MED | Med E | 1 |
| MED | Med F | 1 |
| OTHERS | Others G | 1 |
| OTHERS | Others H | 1 |
If I may add these steps how to create a dropdown list where the options in the second dropdown list (column B) are based on the choices in the first dropdown list (column A).
This can be achieved by using the INDIRECT and Data Validation functions.
Here’s how you can do it:
- On Sheet1, create named ranges for each category. For example, select the cells containing “Lab A”, “Lab B”, and “Lab C” and name the range “LAB” by typing the name in the Name Box (located to the left of the formula bar) and pressing Enter. Repeat this step for the other categories (MED and OTHERS).
- On Sheet2, select the cell where you want to insert the first drop-down list (Column A) and go to Data > Data Validation.
- In the Data Validation dialog box, select “List” from the Allow drop-down list and enter =Sheet1!$A$2:$A$4 in the Source field to create a drop-down list with the categories from Sheet1.
- Select the cell where you want to insert the second drop-down list (Column B) and go to Data > Data Validation.
- In the Data Validation dialog box, select “List” from the Allow drop-down list and enter =INDIRECT(A2) in the Source field to create a drop-down list with options based on the selection in Column A.
Now, when you select a category from the first drop-down list in Column A, the options in the second drop-down list in Column B will update accordingly.
I hope this helps!
