data validation list

[rbacon100]

Hi all...

I have a problem.

I have huge list of data.

What I need is this.

3 cells are doing this.

First cell is drop down list of short names, second cell drop down list is similar but it has really long names in it. So I dont know how to make 3rd cell to make drop down list.

I did second one with offset, named all what I need to name.

But on 3rd cell I cant do that because names in second cell are huge and I cant name list with that names as excel doesnt support that long names.

Is there any way to do this.

I can make sheet like this if that will help.
a 1
a 2
a 3
b 1
b 2
c 3
So maybe some kind of vlookup formula in data validation list or I dont know...

that a b and c are really long names so I cant asign name for that lists and to work with offset.

I hope someone can help me.

And I cant change name of data because that is crucial ill all this.

 

[rbacon100]

lol I found out solution

=OFFSET(Sheet2!$A$1,MATCH($B$3,names,0)-1,1,COUNTIF(names,$B$3),1)

 

[rbacon100]

Actually I have another problem...

That works ok but I want if column before change its value that it clear rest.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B3" Then
Range("C3").ClearContents
End If
End Sub

This works, however, I will have alot of data and I dont wanna write macro for every row.

So columns with drop down lists are A, B and C

I want if A changes B and C goes blank, I mean if A3 changes B3 and C3 goes blank, if A4 changes, B4 and C4 goes blank, and so on

Same if B changes C goes blank.

So if B3 changes C3 goes blank, If B4 changes C4 goes blank

thanks in advance

 

[Claus Busch]

Hi,

Am Sun, 15 Jun 2014 06:46:40 -0700 (PDT) schrieb (e-mail address removed):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B3" Then
Range("C3").ClearContents
End If
End Sub

try:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

If Len(Target) > 0 Then Target.Offset(, 1).ClearContents
End Sub


Regards
Claus B.

 

[rbacon100]

Hi,



Am Sun, 15 Jun 2014 06:46:40 -0700 (PDT) schrieb (e-mail address removed):








try:

Private Sub Worksheet_Change(ByVal Target As Range)



If Intersect(Target, Range("B:B")) Is Nothing Or _

Target.Count > 1 Then Exit Sub



If Len(Target) > 0 Then Target.Offset(, 1).ClearContents

End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Wow. That was fast.

And it is working like a charm, I just added same as you wrote, just for column A too.

Thanks for this, you are awesome

 

[rbacon100]

Actually my bad, it isnt working when I add for column A too.

I can only make for one of them, if I make if A column changes then b and c will clear, and I can make that based on your formula. But cant make in same time that if b changes that c only clears.

I can only make one or another. Cant make both

 

[Claus Busch]

Hi,

Am Sun, 15 Jun 2014 08:28:00 -0700 (PDT) schrieb (e-mail address removed):

Actually my bad, it isnt working when I add for column A too.

try:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

If Len(Target) > 0 Then Target.Offset(, 1).ClearContents
End Sub


Regards
Claus B.

 

[rbacon100]

Hi thanks...

I tried like that, but it doesnt clear column C if I change Column A. It clears only column B not C

 

[Claus Busch]

Hi,

Am Sun, 15 Jun 2014 09:24:31 -0700 (PDT) schrieb (e-mail address removed):

I tried like that, but it doesnt clear column C if I change Column A. It clears only column B not C

sorry, I thought it should clear B if value is entered in A and C if
value is entered in B
If it should clear C if you enter in A or in B then:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

Cells(Target.Column, 3).ClearContents
End Sub


Regards
Claus B.

 

[rbacon100]

No worries. I thought also that if I add your first code with A:B one that it should work, but it doesnt. It only works what you write first.

btw, that Cells(Target.Column, 3).ClearContents, I am not sure, but I cant see that is doing anything here.

Maybe to add this, if column B is blank, clear C if that will work. Because column A when it changes column B is empty and column C isnt. So maybe this will work

 

[Claus Busch]

Hi again,

Am Sun, 15 Jun 2014 10:29:48 -0700 (PDT) schrieb (e-mail address removed):

btw, that Cells(Target.Column, 3).ClearContents, I am not sure, but I cant see that is doing anything here.

sorry, my bad.
That should be cells(target.row,3)

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

Cells(Target.Row, 3).ClearContents
End Sub


Regards
Claus B.

 

[rbacon100]

Thanks, but that didnt worked on column A, I mean when changing column A...

But I found solution with your previous formula.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:b")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

If Len(Target) > 0 Then Target.Offset(, 1).ClearContents
If Intersect(Target, Range("b:b")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

If Len(Target) = 0 Then Target.Offset(, 1).ClearContents
End Sub

 

[Claus Busch]

Hi,

Am Sun, 15 Jun 2014 11:24:28 -0700 (PDT) schrieb (e-mail address removed):

Thanks, but that didnt worked on column A, I mean when changing column A...

then you are doing something wrong.
Please look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "ClearContents"
You have to rightclick and download the file because macros are disabled
on OneDrive


Regards
Claus B.

 

[rbacon100]

Thanks but I think you missunderstooded me before, because B stays there if I change or remove A.

As I said before, if I change A, column B and C will get empty. Also if A stays and I change B it will clear C.

That was problem before, that I couldnt figure it out why it isnt working. But I figured it out with that code that before your message.

 

[Claus Busch]

Hi,

Am Sun, 15 Jun 2014 11:53:54 -0700 (PDT) schrieb (e-mail address removed):

Thanks but I think you missunderstooded me before, because B stays there if I change or remove A.

sorry for misunderstanding
Try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

Select Case Target.Column
Case 1
Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).ClearContents
Case 2
Cells(Target.Row, 3).ClearContents
End Select
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 15 Jun 2014 21:00:25 +0200 schrieb Claus Busch:

Private Sub Worksheet_Change(ByVal Target As Range)

or:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

Select Case Target.Column
Case 1
Target.Offset(, 1).Resize(, 2).ClearContents
Case 2
Target.Offset(, 1).ClearContents
End Select
End Sub

Or look again in OneDrive


Regards
Claus B.

 

[rbacon100]

Thank you for this.

Both codes are working great.

Just a question, what is difference between first and second code?

I mean, they are both doing same, but codes are slightly different.

Thank you again for this

 

[Claus Busch]

Hi,

Am Sun, 15 Jun 2014 13:40:58 -0700 (PDT) schrieb (e-mail address removed):

Just a question, what is difference between first and second code?

the only difference is the setting of the ranges.
The first set the ranges with the target.row the second with
target.offset


Regards
Claus B.