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!
