Excel: Dropdown Help

February 4, 2011 at 10:57:04
Specs: Windows XP
I have dropdown list. If I type in select Spam on A1, B1 will auto select No and C1 will select Close.

But If I sellect in B1 Yes, C1 will auto select Resolved.

A1 B1 C1
(Spam/Write/Read) (Yes/No) (Resolved/Open/Close)

Please help me with these.

Thank you.

Makiko


See More: Excel: Dropdown Help

Report •


#1
February 4, 2011 at 15:01:41
re: I have dropdown list.

Where is this list? In A1 only?

Does B1 also have dropdown?

Does C1 also have a dropdown?

Are you asking that the dropdowns in B1 and C1 automatically change values based on certain conditions but also give you the ability to manually change them?

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


Report •

#2
February 4, 2011 at 23:19:54
In every cell, there is a drop down list

A1
(Spam/Write/Read)

B1
(Yes/No)

C1
(Resolved/Open/Close)

I want these to make auto change value
eg.:
1) If I select "Spam" under A1, B1 will automatically select "No" and C1 select "Close"
2) If I select "Yes" under B1, C1 will automatically select "Resolved"

I want to know also, why when I type few words written on cell, it not goes to auto complete?
eg.:
If I manually type "Sp" under A1 or something that begins with the word listed on drop down, it will not auto complete, I need to type everything.


Report •

#3
February 5, 2011 at 14:10:50
Why are you typing something in a cell that has a drop down? If you want Spam, why don't you just choose it from the list? In any case, Autocomplete does not read the list specified for the drop down, so you are not going to get that to work. But I'd still like to know why you are manually entering data in cell where you have placed a drop down.

As far as getting the other cells to automatically show a specific value based on the value in another cell, you'll need to use VBA.

Right click the sheet tab for the sheet you want this to happen in and choose View Code.

Paste this into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
   If Target.Address = "$A$1" Then
      If Target = "Spam" Then
        Range("B1") = "No"
        Range("C1") = "Close"
      End If
   End If
'
   If Target.Address = "$B$1" Then
      If Target = "Yes" Then
        Range("C1") = "Resolved"
      End If
   End If
 Application.EnableEvents = True
End Sub

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


Report •

Related Solutions

#4
February 6, 2011 at 03:05:18
I really appreciate your prompt response.

How can I made this code effective from Cell 1 to Cell 100? And also I would like to add in D1. Anything I would type in D1 will auto copy from D1 to D100.

Thank you,

Makiko Yamamoto


Report •

#5
February 6, 2011 at 11:39:33
re: "Cell 1 to Cell 100"

What do you mean by Cell 1 to Cell 100?

Cells typically require a column and row to identify them, not just a "number".

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


Report •

#6
February 7, 2011 at 07:20:54
Eg.: I write Makiko Yamamoto on D1, on D2 to D100 will auto fill and copy D1.

Report •

#7
February 7, 2011 at 08:00:43
You've confused me!

You asked for code to autofill B1 & C1 based on the value chosen in A1.

I have supplied code for that.

You then asked to have the code modified to work for Cells 1 to 100.

I asked what you meant by that and you told me that you want to autofill D1 to D100 based on what is in D1.

Here's the problem:

The code has nothing to do with Column D, so I don't understand your request to make the "code effective from Cell 1 to Cell 100"

I'm guessing that you are making 2 separate requests, but I'm really not sure.

Please clarify.

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


Report •

#8
February 7, 2011 at 08:59:58
I am very sorry Derby if I make you confused. From the code I requested from A1,B1 and C1, I just want to add more option on D1.

I would like to request if you can add code. Because what I want to do is...
If I type my name on D1 "Makiko Yamamoto", D2 to D100 will auto write my name too without writing it one by one each cell (D2 to D100)..

Ex:
D1
Makiko Yamamoto
D2
Makiko Yamamoto
D3
Makiko Yamamoto
D4
Makiko Yamamoto

- up to D100 -


Report •

#9
February 7, 2011 at 13:30:16
And one more thing about the code you gave. This codes is for just

A1, B1, C1

how can I use this to

A2, B2, C2
to
A100, B100, C100

Thank you


Report •

#10
February 7, 2011 at 17:23:09
Here's a tip for posting in a help forum such as this one:

Please don't try to make the task simpler but leaving out parts of your requirements, especially if you don't know how to make the modifications yourself. For example, if you are looking for code that needs to work on a range of cells (e.g. A1:C100) don't ask for code that only works on A1:C1.

In the end, you just end up making more work for those that supply the solutions since they have to modify the code and set up a testing spreadsheet (again) to make sure the code does what it's supposed to do.

The more details you supply up front, the better.

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
   If Not Intersect(Range(Target.Address), Range("A1:A100")) Is Nothing Then
      If Target = "Spam" Then
        Range("B" & Target.Row) = "No"
        Range("C" & Target.Row) = "Close"
      End If
   End If
'
   If Not Intersect(Range(Target.Address), Range("B1:B100")) Is Nothing Then
      If Target = "Yes" Then
        Range("C" & Target.Row) = "Resolved"
      End If
   End If
'
   If Target.Address = "$D$1" Then
      Range("D2:D100") = Range("D1")
   End If
 Application.EnableEvents = True
End Sub

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


Report •


Ask Question