Multi-column Validation list (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote