• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining Data Validation List and Manual Entry

R

rks

New Member
There are two columns A and B. When the user manually enters country name in cells under column A, two scenarios arise:

1. If country name entered is " India", the adjacent column should display a drop down list for user to choose from. (List entries North , South, East, West)

2. if country name entered is any other country name,the adjacent column should display " Not Applicable"


Kindly let me know if this is possible without macros; just using formulas. I tried googling it out but no success. Kindly help.
 
Good day rks


The upload in my dropbox should be of help


https://dl.dropbox.com/u/34893656/Data%20Validation.docx
 
Hi Bobhc. Thanks for that. My problem is Column A does not have a dropdown list. The country name is manually entered. If it's India then adjacent cell B shows a dropdown list( North ,South, east and West). Else it displays a text "Not Applicable" in Column B for any country entered manually.
 
rks


Why not have a drop down list with country names, would make it easyer for those using the spreadsheet and eliminate input mistakes.
 
Hi Bobhc, creating a drop down list with pre-defined country names is difficult as it is not possible to predict the country from which we get request.
 
Hi ,


You can try this :


In column B , for each cell starting from B2 , use a Data Validation drop-down , which is a list , with the following formula :


=IF(A2="India",Zone_List,Not_Applicable)


Define two named ranges , Zone_List , which will have the regions North , South , East and West , and Not_Applicable , which will have only one item Not Applicable.


Now , when you enter India in A2 , B2 will display the drop-down having the 4 regions ; if you enter any other country name in A2 , B2 will display the drop-down having only one item Not Applicable ; to display this drop-down automatically , you can use the following VBA code :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Target.Offset(, 1).Select
Application.SendKeys ("%{DOWN}")
End Sub
[/pre]
Narayan
 
Hi Narayan991,thanks for your suggestion. I tried your suggestion,( not yet the VBA code). I named two ranges as Zone_List and other as Not_Applicable suggested by you. However when i enter other countries, it directly shows Not applicable( which is fine). But when i enter "India", it shows either North or South or East or West( but not a drop down). Kindly help.
 
Hi ,


That means you have not selected List from the Data Validation options ; when you click on Data Validation , in the Allow options , select List ; in the Source box , enter the formula.


When you enter India in a cell in column A , the corresponding cell in column B will have a drop-down list available ; the same applies when you enter any other country in a cell in column A. The only difference is that one drop-down ( the one for India ) , will have 4 choices , while the other one will have only one choice.


Narayan
 
The first part is working perfectly fine. Thanks a ton. Kindly let me know if there are any other way to display "Not applicable" other than using the code( as am a starter and finding it difficult to use Macros)
 
Hi ,


The regions as well as the Not Applicable option are displayed through the Data Validation drop-downs in column B.


The drop-down list will be displayed only through the macro ; else the user has to click on the drop-down arrow to display the list. There is no other way.


The macro is not complex ; if you want to know where to copy / paste this , I can upload a sample workbook containing this macro , which you can then apply to your own workbook.


Narayan
 
Hi Narayank ,


I tried this code. Since i am working in Mac machine when i run the above VBA code, it gives me error showing that the line :Application.SendKeys ("%{DOWN}") is not supported in Mac. Kindly help
 
Hi ,


Sorry but from what I have googled and found , there is no alternative. In case you get a helpful answer from anywhere else , please post it here so that it helps others.


Narayan
 
if you google Indirect, Contextures has an excellent tutorial on this exact topic. In fact, some of the examples even use your similar criteria.


http://www.contextures.com/xlFunctions05.html#DataVal
 
You must log in or register to reply here.
Back
Top