Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-column Validation list (Excel 2000)

    Can I use a list (whether it be a named range, or just a highlighted region of cells) for a Validation list? When I click on the drop-down arrow for say column D's validation list, can I have more then 1 column for a choice, or is using a combo box the only method? Example

    Red 1
    Green 2
    Blue 3

    Can I see this in a validation list? It must come from two columns of info, and I don't want to concatenate those two columns just to see them in the drop down list box as a choice.

    Just curious on this one.
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-column Validation list (Excel 2000)

    Yes, you can have a range of cells, one column wide. Just go to Insert > Name > Define, give the range a name (no spaces) (i.e. Source), identify the range, click Add.

    Then go to Data > Valdiation > select list from the dropdown list, and in source type "=Source" (without the quotes). Click Okay.

    For multi-column, you will need the combo box, I think.

    The Concatenate function is not difficult. Set it up for one cell (i.e. D3) which has =concatenate(b3,c3) or if you want a space between them =concatenate(b3," ",c3) . Then fill down (by double-clicking the fill symbol, or by dragging in the normal "fill" way).

    If you name the range of concatenated values (as above), then you can reference it in the validation reference (see above), and then hide the column that has the concatenation (i.e. column D). Should be the best of both worlds.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-column Validation list (Excel 2000)

    I'm not sure what you are asking. Is Red in one column and 1 in the second column, or is Red 1 and Green 2 in one column and Blue 3 and Yellow 4 in the second column? Either way, you can not do this with data valadation without creating another column that contains the concatenations (case one above) or the complete list.
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-column Validation list (Excel 2000)

    Yes, it seems that concatenation is the answer, (easiest anyhow). I took =A1&" "&B1 and joined those 2 columns together, and then used new column of concatenated information in a validation list box. That worked swell. I was just seeing if there was a way around it. Couldn't seem to just have 2 columns in the drop down box which is great in an access form using a combo box. The same techniques in Excel did not give me the same results. I could highlight several columns if I wanted to, but the list would only show what was in the first column. Will just have to make do with the concatenation. It works, right?
    Thanks so much guys...
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-column Validation list (Excel 2000)

    Have your tried looking at a Multi-Column Listbox? The attached ZIP file contains an XL file that provides examples of VBA code for a variety of userforms including Multi-Column listboxes. I don't recall where I found the file on the Net but it is a good resource.

    John
    Attached Files Attached Files

Posting Permissions

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