Chatting about Excel and More

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 Contextures.com. 

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.