+ Reply to Thread
Results 1 to 12 of 12

data validation list selected from a range

  1. #1
    Native
    Guest

    data validation list selected from a range

    Hello:

    Have searched for this but figure either I'm calling this the wrong
    thing or is a limitation.

    I have a range of cells I would like the data validation to choose
    from. For example:

    1 4 9 12
    56 3 24 1
    8 18 49 3

    I'd like all of these #'s to be in my list to select from; however, all
    I can get the list to accept is just one column.

    Any advice?


  2. #2
    Dave Peterson
    Guest

    Re: data validation list selected from a range

    You can put all your data in a column to retain the dropdown or you can validate
    the cell using a custom formula:

    With A1 the cell getting Data|Validation and the numbers in A15:D17, I used this
    formula:
    =COUNTIF($A$15:$D$17,A1)>0

    Native wrote:
    >
    > Hello:
    >
    > Have searched for this but figure either I'm calling this the wrong
    > thing or is a limitation.
    >
    > I have a range of cells I would like the data validation to choose
    > from. For example:
    >
    > 1 4 9 12
    > 56 3 24 1
    > 8 18 49 3
    >
    > I'd like all of these #'s to be in my list to select from; however, all
    > I can get the list to accept is just one column.
    >
    > Any advice?


    --

    Dave Peterson

  3. #3
    Gord Dibben
    Guest

    Re: data validation list selected from a range

    Type them into the source dialog.

    1,4,9,12 etc.

    Note the comma-delimited format.


    Gord Dibben MS Excel MVP

    On 19 Jan 2006 08:52:16 -0800, "Native" <[email protected]> wrote:

    >Hello:
    >
    >Have searched for this but figure either I'm calling this the wrong
    >thing or is a limitation.
    >
    >I have a range of cells I would like the data validation to choose
    >from. For example:
    >
    >1 4 9 12
    >56 3 24 1
    >8 18 49 3
    >
    >I'd like all of these #'s to be in my list to select from; however, all
    >I can get the list to accept is just one column.
    >
    >Any advice?



  4. #4
    Native
    Guest

    Re: data validation list selected from a range

    Dave: tried your formula but keep getting the same message:

    "The List Source must be a delimited list, or a reference to a single
    row or column."

    Also, I can't type in all the values b/c the list is ever changing
    hence I have an array name that references a range.


  5. #5
    Native
    Guest

    Re: data validation list selected from a range

    Dave: tried your formula but keep getting the same message:

    "The List Source must be a delimited list, or a reference to a single
    row or column."

    Also, I can't type in all the values b/c the list is ever changing
    hence I have an array name that references a range.


  6. #6
    Dave Peterson
    Guest

    Re: data validation list selected from a range

    It doesn't use a list. It uses a custom formula.



    Native wrote:
    >
    > Dave: tried your formula but keep getting the same message:
    >
    > "The List Source must be a delimited list, or a reference to a single
    > row or column."
    >
    > Also, I can't type in all the values b/c the list is ever changing
    > hence I have an array name that references a range.


    --

    Dave Peterson

  7. #7
    Native
    Guest

    Re: data validation list selected from a range

    ps - the error message was in List mode. When I put it in custom the
    validation didn't work. Meaning I could type in anything and it would
    allow the input.


  8. #8
    Native
    Guest

    Re: data validation list selected from a range

    But the validation part of the data validation didn't seem to work in
    custom. Using the above #s from my original post, if I typed in 124 I
    would not get an input error message.


  9. #9
    Debra Dalgleish
    Guest

    Re: data validation list selected from a range

    If there are blank cells in the named range, remove the check mark from
    'Ignore blank' in the data validation dialog box.

    Native wrote:
    > ps - the error message was in List mode. When I put it in custom the
    > validation didn't work. Meaning I could type in anything and it would
    > allow the input.
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  10. #10
    Native
    Guest

    Re: data validation list selected from a range

    bingo! (take the check mark out)

    also, i played around with it and if I took the range name out and just
    used the cell references it worked with the check still on ????

    is there anyway to get this to work with a list? (ie-a pull down)


  11. #11
    Dave Peterson
    Guest

    Re: data validation list selected from a range

    Post the formula you used. And the addresses that contain the table and the
    address of the cell that got the format|conditional formatting formula.

    Native wrote:
    >
    > But the validation part of the data validation didn't seem to work in
    > custom. Using the above #s from my original post, if I typed in 124 I
    > would not get an input error message.


    --

    Dave Peterson

  12. #12
    Debra Dalgleish
    Guest

    Re: data validation list selected from a range

    As Dave said, you'd need the data in a single column if you want to
    create a dropdown list.

    Native wrote:
    > bingo! (take the check mark out)
    >
    > also, i played around with it and if I took the range name out and just
    > used the cell references it worked with the check still on ????
    >
    > is there anyway to get this to work with a list? (ie-a pull down)
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1