dependant lists and manual text entry problem

[New2You]

Hi all,

I am relatively new to Excel. I have figured out how to use named ranges as lists and use INDIRECT Data Validation to have a list's selection made in one cell populate a new list in an adjacent cell.

However, I have a situation I cannot figure out to save my life.

A1 contains a list of "Characters" (The choices are "Numbers", "Letters", or "Other")
Based on the choice in A1, B1 has to populate with the following:
- A list of available Numbers to chose from (1,2,3..etc)
- A list of available Letters to chose from ( A,B,C...etc)
- Text that instructs the user to enter text into the cell manually (For example, their "Other" character might be something like "@#$%^&*!". (Not unlike my mental state trying to figure out this problem.) I want them to see the instruction to "ENTER CHARACTERS HERE" and then be able to type in the cell.

So I need B1 to either populate with a menu that is dependent on the menu selection in A1, or allow for free text entry depending on if they chose the "other" option in A1.

Ideas? And if there is VBA or other advance coding required keep in mind I only know enough to be a danger to myself and others and may need some specific details on how to achieve this.

Thanks in advance - this place looks like a wealth of knowledge.

-New2You

 

[AlphaFrog]

• Right-click on the sheet tab
• Select View Code from the pop-up menu
• Paste the code below in the VBA edit window

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$1" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Value = "Other" <SPAN style="color:#00007F">Then</SPAN><br>            Range("B1").Value = "ENTER CHARACTERS HERE"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Range("B1").Value = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The part of the code that is...
<font face=Courier New>        <SPAN style="color:#00007F">Else</SPAN><br>            Range("B1").Value = ""</FONT>
...will clear cell B1 when anything besides Other is entered in A1. You may or may not want this. If not, just delete it.

 

[New2You]

Thanks for the quick reply today!
I tried as you suggested and can see how the adjacent cell will populate with the instructions I want for the user. This works quite well and the user can then enter text if there are no other menus involved.

However, I also wanted to incorporate drop down menus. So if a user picked "Numbers" from A1's drop down menu, B1 would use Data validation (=INDIRECT(A1)) to load a specific named list into a drop down menu giving them a menu with number choices to pick from. If the user chose "Letters" from A1's drop down menu a different drop down menu with letter choices would appear in B1 (again using that INDIRECT(A1) function and a named list.)

If I use your method, the B1 field populates with my instructions to enter characters, but if the user tries to enter characters the application tells him the text entered is not from the required list.

I need to force that cell to ignore the INDIRECT data validation in B1 if "Other" is chosen in A1.

Also - I will need to repeat this functionality repeatedly from Cells A2 and B2 on through A300:B300 (maybe further.) I'm not sure if the VBA code will allow for that replication?

We're getting close...I think.

Can you suggest how to get us all the way there?

Many thanks for the help so far.

 

[AlphaFrog]

If I use your method, the B1 field populates with my instructions to enter characters, but if the user tries to enter characters the application tells him the text entered is not from the required list.

On the Data Validation dialog, you can turn off the warning message to require the entry be from the list. See the Error Alert tab on the Data Validation dialog.

You may want to make a named range called Other that's just one blank cell or is a list of predefined "Other" inputs.

The code below will put "ENTER CHARACTERS HERE" in column B of the same row where the cell in column A has "Other" selected..

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Value = "Other" <SPAN style="color:#00007F">Then</SPAN><br>            Target.Offset(, 1).Value = "ENTER CHARACTERS HERE"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Target.Offset(, 1).Value = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[New2You]

Excellent! That did the trick! Thanks so much for the awesome advice and code! I'm sittin' pretty now.

Best,
New2You