dependant lists and manual text entry problem

New2You

New Member
Joined
Sep 27, 2011
Messages
8
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. :eek:

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

-New2You
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
  • 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.
 
Last edited:

New2You

New Member
Joined
Sep 27, 2011
Messages
8
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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

New Member
Joined
Sep 27, 2011
Messages
8
Excellent! That did the trick! Thanks so much for the awesome advice and code! I'm sittin' pretty now.

Best,
New2You
 

Watch MrExcel Video

Forum statistics

Threads
1,129,439
Messages
5,636,296
Members
416,911
Latest member
jafornwalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top