I have data on another sheet in the same workbook that I am using as a source for a dropdown. It works in Excel 2010, but not in Excel 2007. I tried naming my list and using source =myRangeList where myRangeList is the name of the list. Again, it works in excel 2010, but not in excel 2007.

Anybody know what why it doesn't work in Excel 2007?


I solved this by having both Excel 2007 and 2010 installed.In Excel 2010 I selected the cell that should have contained the dropdown-list and then pressed data validation from the data tab. I just copied the values in excell 2007 and saved.

If you find the sheet to be protected, you can use this solution : http://mcgimpsey.com/excel/removepwords.html

Hope it helps

  • This worked for me. I think the problem is that 2007 doesn't let you select a range on another sheet, though you can type it in the box, making sure you include the sheet name. May 23 '13 at 15:18

Since the range is on another sheet, make sure the sheet name is included in the name definition. If you use the name on sheet1 but the range is on sheet2 make sure your name gets defined in sheet1 as

Refers to: =Sheet2!$A$1:$A$10


Yes, the above statement will just about get you there.

However, there is a key step missing.

1) Create your list in Excel i.e. in Sheet 2.

The list may be:

  • Fruit
  • Apple
  • Orange
  • Pear

2) Select the list, highlight all cells.

Go to Data > Data Validation > Allow - list, source and highlight cells in the list, click OK.

3) The list populates.

4) Use / reference this list in another Sheet in the workbook.

5) Copy a cell from the list from Sheet 2, paste into the cell you want to use the drop down from in Sheet 1.

6) Click in the cell in Sheet 1, select data validation, in the cell reference - highlight the entire list and click OK.

7) You will see the reference =Sheet2!$A$1:$A$3 - hey presto the list works

End of command.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.