Creating a strange list :S

[Guest]

I was given a strange query from one of my users today in Excel, and I must
admit I found myself clueless as to how to help her. Basically she wants to
create a drop down list (easy to do), when she makes a selection on that list
she wants it to appear in a different cell (not so easy), on top of that,
once she has made that selection, she then wants to click on the same list
again and make a different selection and have that appear on the same cell as
the before, but with a comma seperating the two selections. Is there any way
to do this?

 

[Guest]

You'd have to use a Worksheet_Change event in VBA, if that's something you
want to delve into

 

[Gord Dibben]

Phil

See Debra Dalgleish's site for a sample workbook that does just this.

Allows multiple selections to be placed in one cell.

DV0017 - Select Multiple Items from Dropdown List-- Select multiple items from a
dropdown list; an event macro stores selections in adjacent cell, or in same
cell. DataValMultiSelect.zip 18kb updated 22-Feb-07

http://www.contextures.on.ca/excelfiles.html#DataVal


Gord Dibben MS Excel MVP

 

[Guest]

Hi,

Thank you both for the help. Gord, that was exactly what I was looking for!
Can you help me just a little more though please? I'm no VBA expert. Where
can I find the code that gave the list of values (One, Two, Three) so I can
customize these to something more relevent?.

Thanks again,

Phil(Tech)

 

[Guest]

Hi,

This is very useful for something I am trying to sort out too, cheers.

Is it possible to select from the drop down and have items appear on a
separate rows as in the demonstration, but to also fill out associated cols.

So if in the List validation it looks at AA1:AA10, and returns the selected
item into column B, could it at the same time return into column C what is
against the selected item in AB1:AB10?

I hope I have explained that clearly enough! - I imagine it would involve
somekind of lookup? - I don't want to have any formulas etc in the C col
until something appears in the B column.

Thanks

Peter

 

[Zilla]

Right-click on the worksheet tab and choose "View code."

 

[Gord Dibben]

Phil

The list of One, Two, Three etc. is hard-typed into the DV Custom List.

You must make your own list and use that list as the range to select from.

You could hard-type into the list dialog box(comma separated) or if list is more
extensive like A1:A20, use that range as the source in the dialog box.

If the list is on another worksheet, create a name for the range and enter
=MyList


Gord