Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: Source for a Pulldown listll

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by palto1
    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
    Last edited by shades; 07-28-04 at 10:41.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    68

    pulldown list in Excel

    Thanks so much - I will try that!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •