Values from 3 separate drop-down lists entered into single cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Smatass - 16 Sep 2007 05:58 GMT

I have 3 columns for 'complaint A', '..B', '..C'. Each of the cells in
those columns has the same drop-down list that contains 5 items. What I want
to do is have the item from the drop down list moved/placed automatically
into a new cell, but I want the other two drop-down lists to have their
selections moved/placed into the same cell as the first drop-down list
without overwriting the first entry. The 3 entries in the single cell would
be comma separated ideally. Does that make sense and is it possible? Any
help is greatly appreciated. Thank you.

Reply to this Message

OssieMac - 16 Sep 2007 06:26 GMT

You have not said what sort of drop down list you have but I assume that the
results of the selections are displayed in separate cells. If my assumption
is correct, assuming that the 3 separate results are in cells A1, B1 and C1
then you can concatenate the results into another cell.

=A1&", "&B1&", "&C1

The characters between the double quotes can be any characters you like to
use for the separation. In the above I have used a comma and a space.

Regards,

OssieMac

> I have 3 columns for 'complaint A', '..B', '..C'. Each of the cells in
> those columns has the same drop-down list that contains 5 items. What I want
[quoted text clipped - 4 lines]
> be comma separated ideally. Does that make sense and is it possible? Any
> help is greatly appreciated. Thank you.

Reply to this Message

Rick Rothstein (MVP - VB) - 16 Sep 2007 06:42 GMT

If all the complaint cells do not have to be filled in, and if maintaining
an identity to which column(s) the complaint(s) came from, then this
modification of your formula will look neater...

=A1&IF(B1="","",IF(A1="","",", "))&B1&IF(C1="","",IF(AND(A1="",B1=""),"",",
"))&C1

If maintaining an identity to the complaint columns is required, then
perhaps this modification to your formula would be better for the OP to
use...

=IF(A1="","<?>",A1)&", "&IF(B1="","<?>",B1)&", "&IF(C1="","<?>",C1)

Rick

> You have not said what sort of drop down list you have but I assume that
> the
[quoted text clipped - 24 lines]
>> Any
>> help is greatly appreciated. Thank you.

Reply to this Message

Smatass - 16 Sep 2007 09:30 GMT

Thank you for the quick response. That worked, but not exactly the way I
want it to. After re-reading my post I didn't explain what I want very well.
Your formula is the end-result I want, that works perfect. I want more of
an automated process I guess since I am not the one that is going to be using
this. I would like the selections from the 3 drop-down lists to be placed
into the final cell as soon as they are selected. so if the first drop down
is in cell A1 and final cell is in A4, when the user selects 'Z' from the
drop-down then A4 will display 'Z,' at the same time. Your formula works
perfectly for this, but the only problem with that is each selection is going
to be displayed twice on each row. for example if drop-down lists 1-3
display 'a' 'b' 'c' then the final cell is going to display 'a,b,c' which I
can foresee being extremely confusing to the users, and kind of redundant
when printed. The only thing I can think of is to make the 3 drop-down lists
static, and create a macro that will input the selection into the final cell.
Do you have any ideas for that?

> If all the complaint cells do not have to be filled in, and if maintaining
> an identity to which column(s) the complaint(s) came from, then this
[quoted text clipped - 39 lines]
> >> Any
> >> help is greatly appreciated. Thank you.

Reply to this Message

Rick Rothstein (MVP - VB) - 16 Sep 2007 10:17 GMT

I'm going to sleep now, so I'll have to look into this after I get up
(unless someone else answers your question before then). I do have a top of
the head idea you might consider... make the font color the same as the
background color for the three cells with the drop downs in them. That way,
the text will still be entered into the cell, but the user won't see it
(except in the formula bar, but there the user will only see one cell at a
time, so that shouldn't be confusing) and the formula will still work as it
does now. Anyway, it's a top of the head idea from someone who is very
sleepy. I'll check back in a few hours to see if you have posted anything
back here.

Rick

> Thank you for the quick response. That worked, but not exactly the way I
> want it to. After re-reading my post I didn't explain what I want very
[quoted text clipped - 72 lines]
>> >> Any
>> >> help is greatly appreciated. Thank you.

Reply to this Message