Add/create drop down list if text is entered (on the fly)?

Microsoft Office 2010 professional (full...
December 18, 2014 at 12:25:00
Specs: Windows 7
Hello all

Is it possible to make a drop down list appear if I would to enter @@ in a cell.

An example: If I am writing a text in a cell and I needed to insert a special value, then I could type:

Hello Mr. @@

And then a drop down menu would be created where the two @'s are placed. Then I could select my value from that drop down menu, and then keep typing until I needed a new value and so on.

Is this possible to make?

/Jacob


See More: Add/create drop down list if text is entered (on the fly)?

Report •

#1
December 18, 2014 at 12:38:09
In general, any given cell can only contain the drop down or the text, not both.

Consider where they place the Drop Down feature: it's contained within the Data Validation section of Excel. In other words, a Drop Down is supposed to be used to ensure that only the text that the author of the sheet wants in the cell can be placed there.

So, to answer your exact question, No...what you are asking for can not be done.

That said, we often get "generic" questions in this forum that don't really detail exactly what the requirements are. Fairly often, once the real requirements are laid out, a different solution becomes available.

So tell us, what exactly are you trying to accomplish?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 18, 2014 at 13:38:53
Okay, so text and drop down menu can't be in the same cell.

The problem is, that I need to write individual texts to each customer and in the text I need to put in values from the customers row of data. At the moment I have a solution where I can write a text and insert the values that relates to the customer by using substitutes like @@road@@, @@city@@, @@gender@@ and so on. When I have written the text with those substitution words the text is put into a cell in the customers data row and the substitution words are replaced by the values that relate to those substitution words. Its a bit like merge fields work in practice I think..

Well the problem with this solution is, that the users of the excel sheet has to remember those exact @@ @@ substitution texts for this to work. It would be much more user friendly if they just could type @@ and then select the substitution words from a list (like a drop down menu).

I have a few questions, now that I know that a drop down menu can't be in the same cell as text:

1. Can you write a text in one cell and select a drop down menu in another cell without jumping/exiting the textcell you are writing the text in?

2. If this is possible, can some code be made, so that the value selected on the drop down menu gets put into the cell you are currently typing text into?

I can see, that a simple list of those available @@ words could be placed in a column next to the cells you were to write those individual texts in, but first, you would have to type the @@ words, and second you would have to type them exactly as they were named in the SUBSTITUTE code for them to work.


Report •

#3
December 18, 2014 at 13:53:14
Presumably you are hoping to use Excel for this? Presuming so, is it essential to use Excel? Word allows a range of drop down, select, fill in form etc. routines, which might be suitable?

Report •

Related Solutions

#4
December 18, 2014 at 14:18:06
Yes I need this to be in excel (I guess) because all my data is in excel and I need the data to be automaticly put into the customers rows in excel, so that I can merge the data from there to my mail templates.

Report •

#5
December 18, 2014 at 15:06:23
Dun a wee trawl via google using the string/search words:

create a dropdown list in excel

and this was top of the list; with much more below it.

http://tinyurl.com/cgfge68

May get you going in the right direction, or one of the other hits similarly (if you duplicate my trawl as above)?


Report •

#6
December 18, 2014 at 19:05:17
First, allow me to sort of echo trvlr's point...

You are trying to use Excel for a purpose that it was not designed for. Excel is a numbers based application, not a text editor. Yes, they built in some text related functions, but they are there more for convenience sake than as a major player.

re: 1. Can you write a text in one cell and select a drop down menu in another cell without jumping/exiting the textcell you are writing the text in?

No.

re: 2. If this is possible, can some code be made, so that the value selected on the drop down menu gets put into the cell you are currently typing text into?

Since it's not possible, what you specifically asked for can't be done. However, there is a possible work around.

Why not have some cells where you manually enter text and other cells with drop downs for your customer data and then a final "output" cell with all the other cells concatenated together?

e.g.

In A1 you would enter A man named Mr.
In B1 you would have a drop down with a list of names.
In C1 you would enter has a red beard.
In D1 you would have =A1&” "&B1&" "&C1

This would result in single cell with all 3 entries.

A man named Mr. name from drop down has a red beard.

I am aware that this will only work in certain situations, but I'm just tossing it out as an idea.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
December 18, 2014 at 21:04:45
Thanks for your suggestion, but I think that this will be hard to implement.

Any chance that some code can be run before you exit a cell? Like if I was typing in a cell and would enter a keyword, that keyword would run some code at the moment it was finished getting typed? Some sort of autocorrect trigger..?


Report •

#8
December 19, 2014 at 04:05:30
There is a Worksheet_Change event that can be triggered whenever a cell is changed, but the event is captured after you leave the cell, not while you are typing in it. Once you leave the cell, all of the keywords could be replaced.

However, I don't see how that would improve your situation. Wouldn't the users still need to memorize all of the correct keywords?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
December 19, 2014 at 09:51:08
Yes your right, the only way this could work is if they could type their text and if they typed @@ a drop down menu or something similar would popup and they could select the keywords they needed, that would then be inserted instead of the @@'s and they keep on typing. It would be a very very nice feature to have, because we have so many keywords we are going to use in the new worksheet I am working on.

But I guess excel only reacts when you leave a cell, so no chance to every get anything to pop up if your still in the cell...


Report •

Ask Question