PDA

View Full Version : Solved: Dynamically setting a dropdown dependant upon cell value



ScoobyDoo
09-24-2012, 07:39 AM
Hello all,

I have two columns. Column A is where the user types data, column B is where the item is categorised with a dropdown. What I am looking for is a way to dynmacially set the dropdown dependant upon what is entered in column A. So if the word loan is in cell A2 then the dropdown in cell B2 should be set dynamically to "other", without the user selecting. But there should still be a way for the user to categorise other items or edit the dynamically set category. Ideally I would like to do this without VB as it should set as the user is inputting data, but open to what is feasable. I have attached an mockup to demonstrate.

Does anyone know if this is possible? :think:

omp001
09-24-2012, 12:47 PM
Hi Scooby.
See if this could help you somehow.

1. change your range name from Mycategories to loan
2. select B2 >> menu Data Validation >> List >> Source >> =INDIRECT(A2)
3. copy down from B2 as needed

ScoobyDoo
09-25-2012, 03:17 AM
Hi Scooby.
See if this could help you somehow.

1. change your range name from Mycategories to loan
2. select B2 >> menu Data Validation >> List >> Source >> =INDIRECT(A2)
3. copy down from B2 as needed

I see what you're doing here but not quite what I was looking for. With this suggestion if the word 'loan' is entered in A2 then the dropdown in B2 will become active & display the options....Office, Supplies, Food, Other. What I would like if the dropdown in B2 always active with the options but if the word 'loan' is entered in A2 for the dropdown to be set to 'Other' without the user having to choose it.

I'm not entirely sure if this at all possible??:dunno

Teeroy
10-12-2012, 08:16 PM
Hi ScoobyDoo,

From your example I assume you mean the string in column "A" INCLUDES "loan", not just checking for the word on its own so in Sheet1 code container:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
If InStr(UCase(Target.Value), UCase("loan")) > 0 Then
Target.Offset(0, 1).Value = "Other"
End If
End If
End Sub

ScoobyDoo
10-15-2012, 03:55 AM
WOW! Once again Teeroy you've saved the day! The code worked perfectly.

Thanks a million!