This video is in response to a viewer who wants to join two columns containing Data Validation “Pick-from” lists. She knew that there was a Formula that could perform this, but she didn’t know how to accomplish this.
I promised to create this Excel video lesson to show her how – using the =INDIRECT() Function.
Here are the steps to follow in this lesson:
- Create lists for each group you want to be able to select from. Division and then in Departments (Sales, Marketing, etc.)
- Name each of these lists. Use either the NAME BOX or “Insert – Name – Create” (Select the Label and the Values).
- For the 1st Column – Division – use “Data – Validation” and in the “Allow” drop-down, choose “List.”
- Use the F3 shortcut to bring up the Named Range Dialog Box. Select “Division.” Type in a message for the Screen Tip and click OK.
- For the 2nd Column – the one whose “Pick-from” list will be based on the Values from the 1st Column (Division), we start with “Data – Validation” and in “Allow” choose “List.”
- Here is where we use the =INDIRECT() Function in the “Refers To” box. Click to select the cell in the Same Row in the 1st Column. Note: Be sure to make the Cell Reference Relative so that the formula can be copied down the column.
- I created the Validations in the Top Cell in each Column. Use Copy, Paste Special, Validation to apply the “Pick-from” validation to the remaining cells.
This is not a “Perfect” solution. As you can see, when you change the value in the first drop-down list, the value in the second list remains in place. What does change, however, is the list of values now available in the second drop-down list – waiting for you to make your selection!
Looking for a specific Excel Video Lesson? – Index of all Excel Topics
News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.
Link to this post!
Got it all to work as described in the video. One challenge i discovered is when i go to use this again and change the value in the first column to something else from the drop down it does NOT unvalidate or remove data from the second field. For example, if i choose SALES in the first column then TELEMARKETING in the second column all is good. I save the file and go back to use it again starting with the same saved spreadsheet. This time, i replace SALES with MARKETING. The second cell still says TELEMARKETING even though it is no longer a valid drop down option since the first field is MARKETING. What do you suggest? Thanks.
Dan
The Indirect Function will not AUTOMATICALLY update the second column. What it does change is the drop-down selection that “becomes available” in the 2nd column when you select a new value from the drip-down list in the 1st column.
I created an updated video that shows you more information about the Indirect Function and drop-down lists. Here is the link:
http://www.thecompanyrocks.com/how-to-trigger-a-unique-list-of-values-based-upon-another-drop-down-list-in-excel/