How to extract the word in a drop down box and translate to another cell

[baggypants]

Hello
Lets suppose that in a drop down box I have the words : "I" "want" "to" "create" "a" " sentence"
Well what I would like to do is have each word as selected in the drop down box, placed in another cell creating a sentence.
If you picked the words in order the cell should read: I want to create a sentence or it could read any combination of these words depending in the order they were picked. So it could read: I sentence want to create a sentence if the words were selected in this order.
Is there a formula for this??

Thanks a milion!

[Mayda89]

use concatenate function?

[baggypants]

Yes the problem is though that all the words added to the cell will be from a drop down box located in one cell, and building the sentence from there. So all the words from drop down box A2 would need to build in A3. Ex = CONCATENATE(A2 " " ,A2) . Would not work. The sentences could be different lengths as well. The formula is out of my league thats for sure
Any ideas?

[Mayda89]

Hmm, got it. If you want to use one single dropdown to generate every single word in the sentence, then you'll need to write a macro.
To keep things simple, I suggest using more than one dropdown cells to generate words.
For example, if you want a sentence with 6 elements, then make 6 cells with dropdown menus, and then use the concatenate function.

[baggypants]

Holly macro-oni . Well I dont want to do that (Plus, really, I dont have a clue how too)
Well, thanks for the help. maybe somethng will pop up.
Thanks agaain.

[Mayda89]

You mean you don't want to use macros or you don't want to use the alternative solution (with multiple dropdown cells and NO MACROS)?

[baggypants]

Either unfortuantely. Many users will be using this spreadsheet and turning on and off a macro might be too much for them. And spacing out all of the words in different columns would take up way too much space on the spreadsheet. I dont know myself nearly enough about macros, but from what I understand it would have to be automated??

[Mayda89]

hmm, got it.
Then unfortunately there's no other way to accomplish this task :/

[baggypants]

Bummer. Know anything about macros? hehe
Still, I appreciate the help, thanks for putting in your time

[Mayda89]

I have created a sample workbook, with the sample macro written.
Yet, of course the code is written to specifically change the cell D2, according to the value selected from the dropdown at cell C2. The code is just a few lines, it shouldn't be hard to understand and later modify according to your needs.
If you have any questions regarding it, please don't hesitate to ask

I hope this solves your problem!
Cheers

[baggypants]

Wow, that is great! I cant thank-you enough for that!
I really appreciate it!
Two thumbs way up

[Mayda89]

I'm glad that I could help

Take Care!

[baggypants]

That works great and I am super happy with it. I have figured out how to move it around,just where I need it.
Just one question. IF i want a dropdown like this to appear from C2 to say C50 (and write each word beside each column) do I need to write the code individually or can I just put in some kind of range?

[Mayda89]

Is this what you asked for? (see uploaded workbook)

Note that now you can just copy the drop-down cell down as much as you want, the code I wrote does the work no matter which row the drop-down is at, the only thing that matters is that it should be at column c (of course, by modifying the code, you can also change this)

For illustration purposes, I have copied the drop-down down till row 50.

Note2: Since the cell c1 has headers in the file I uploaded, the code does not do anything if the cell c1 is changed, so if you want to change this, just modify the if condition in the code, where it says target.row>1.

hope it helps

[baggypants]

You just made my day, yet again. I am actually even enjoying playing around with Excel too. I never would have guessed. I can only hope if you ever need any help, you find someone as helpful as yourself! I really am smiling.
All the best!

[Mayda89]

Hehe, with your kind words you made my day too .

Take Care!