Hello, I am a newbie to the world of Excel so bear with me! I am trying to create a list box on a column. I would like the drop down list to offer specific values that are on a separate worksheet.

When you use Data>Validation, and you select List as the Validation Criteria, can you select a separate worksheet as the source for your data? If yes, how do you do this?

Thank you,
Grace

Hi, Grace. Yes you can. The easiest way is to name the range on the separate worksheet. Go to that sheet, select the cells (say A2:A10), then go to Insert > Name > Define, and give it a name (no spaces, can't begin with a number). Click Add, then OK.

Now go back to your other worksheet where you want the dropdown list, and select that cell. Go to Data > Validation >Settings in the drop down list of Allowed Criteria, choose List. Then in the box below that type "=" (without the quotes) then hit the F3 key, and it will give you a list of all named ranges in the workbook, select the one you just named, Click OK.

(If you want the original list to be dynamic, i.e. it will grow automatically when you add to the list, so that you don't have to redefine the name, then when you define it, put this in the Refers to box:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

HTH

Thanks so much - I will try that!