Best Microsoft Excel Bloggers

Tuesday, March 23, 2010

I love to use Data Validation to create dropdown lists - particularly when working with Vlookups but I found it irritating that my source list had to be on the same sheet.  After being extremely lazy I finally got around to looking for a work-around and found one at

  1. Create your list
  2. Range Name it - (Select your data, click in the Name Box and type a name and then press Enter)

In this example, I selected my data and created a range name called product.The Name Box is directly above column A.

  • Select a sheet and cell where you want the drop-down list to appear.
  • Select the Data tab
  • Select Data Validation

    Select List from the Allow: drop-down
    In the Source: section, type = product
  • Click OK

Click on the drop-down and see the list you created.

No comments:

Post a Comment

Ms. Excel- Resident Excel Geek