Help w/ User-Defined Function

I posted a similar question earlier but I believe that I may not have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined function which will allow me
to do the following?

1. From a drop-dop box (combo box), select value of either "Yes" or "No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1 with "No". However, if A1 =
"Yes" then B1 brings up another drop-down box (validation list) with another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value was selected in B1) but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below (while "YesNo" is a range containing
YES & NO options on a 2nd worksheet): =IF(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the dependent Yes/No value
(based on "Yes" in A1) has been selected in B1, changing A1 to "No" will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out" the originally stored
function in B1.



Thanks in advance,
Tom

Hi Tom
as said before this can only be achieved with an event macro. Neither a
worksheet function nor a user defined function could achieve this. Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe that I may not have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of either "Yes" or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1 with "No". However,
if A1 =
"Yes" then B1 brings up another drop-down box (validation list) with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value was selected in B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below (while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the dependent Yes/No
value
(based on "Yes" in A1) has been selected in B1, changing A1 to "No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out" the originally
stored
function in B1.



Thanks in advance,
Tom

Frank:

Thanks so much for your help. I put the code into the worksheet that it
doesn't work right now. I'm sure I have made a mistake here... well, or
maybe I didn't provide enough or correct info in the thread. The initial
info was based on sample data.

Would you mind having another look at the additional information? Thanks so
much in advance!!!

****************

Data input/selections are made on worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
....
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
....
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
....
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range on the SourceData
worksheet
.... D2 is referenced to the "YesNo-D" range on the SourceData worksheet
.... E2 is referenced to the "YesNo-E" range on the SourceData worksheet
.... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an event macro. Neither a
worksheet function nor a user defined function could achieve this. Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe that I may not have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of either "Yes" or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1 with "No". However,
if A1 =
"Yes" then B1 brings up another drop-down box (validation list) with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value was selected in B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below (while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the dependent Yes/No
value
(based on "Yes" in A1) has been selected in B1, changing A1 to "No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out" the originally
stored
function in B1.



Thanks in advance,
Tom

Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what you're trying to
achieve. Each column updates the adjacent one? Post some example rows
(plaint text - no attachments please) and describe your expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the worksheet that
it
doesn't work right now. I'm sure I have made a mistake here... well,
or
maybe I didn't provide enough or correct info in the thread. The
initial
info was based on sample data.

Would you mind having another look at the additional information?
Thanks so
much in advance!!!

****************

Data input/selections are made on worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an event macro.
Neither a
worksheet function nor a user defined function could achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1 with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box (validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below (while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1, changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out" the
originally
stored
function in B1.



Thanks in advance,
Tom

Frank:


Currently, column D is dependent on column C.

First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".

Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".

2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.

and so on...

Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.


Any additonal feedback would really help me!!!

Thanks,
Tom




-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.

Would you mind having another look at the additional
information?
Thanks so
much in advance!!!

****************

Data input/selections are made on
worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined
function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out"
the
originally
stored
function in B1.



Thanks in advance,
Tom






.

Hi Tom
not quite sure what should happen with lets say column E if you enter
'No' in C2 but try the following (note: i hardcoded the list an do not
use a named range as you only have two options with 'yes' and 'No'):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:


Currently, column D is dependent on column C.

First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".

Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".

2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.

and so on...

Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.


Any additonal feedback would really help me!!!

Thanks,
Tom




-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.

Would you mind having another look at the additional
information?
Thanks so
much in advance!!!

****************

Data input/selections are made on
worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined
function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out"
the
originally
stored
function in B1.



Thanks in advance,
Tom






.

Frank:

This works just fabulously!!! THANK YOU SO MUCH!

--
Tom


"Frank Kabel" wrote in message
...
Hi Tom
not quite sure what should happen with lets say column E if you enter
'No' in C2 but try the following (note: i hardcoded the list an do not
use a named range as you only have two options with 'yes' and 'No'):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:


Currently, column D is dependent on column C.

First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".

Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".

2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.

and so on...

Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.


Any additonal feedback would really help me!!!

Thanks,
Tom




-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.

Would you mind having another look at the additional
information?
Thanks so
much in advance!!!

****************

Data input/selections are made on
worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined
function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out"
the
originally
stored
function in B1.



Thanks in advance,
Tom






.

Hi Tom
glad it works for you and thanks for the feedback :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

This works just fabulously!!! THANK YOU SO MUCH!

--
Tom


"Frank Kabel" wrote in message
...
Hi Tom
not quite sure what should happen with lets say column E if you
enter
'No' in C2 but try the following (note: i hardcoded the list an do
not
use a named range as you only have two options with 'yes' and
'No'):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:


Currently, column D is dependent on column C.

First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".

Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".

2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.

and so on...

Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.


Any additonal feedback would really help me!!!

Thanks,
Tom




-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.

Would you mind having another look at the additional
information?
Thanks so
much in advance!!!

****************

Data input/selections are made on
worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined
function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out"
the
originally
stored
function in B1.



Thanks in advance,
Tom






.

Frank:

One more follow-up question...

currently, the "YesNo" in column C "drives the other "YesNos" of column D,
E, F, G.

I tried to make a minor change... unsuccessfully though... let's say:

1. "YesNo" in C drives "YesNo" in D
2. If "No" in D, then "N/A" in E; if "Yes" in D then "New York, Miami, Los
Angeles" in E.
3. If "No" in D, then "N/A" in F; if "Yes" in D then "Mike, John, Richard,
Tina" in F.

P.S. There's no link between columns E & F... their values are simply
driven by the "YesNo" selection in D.


Problem... currently, D does not allow to drive E or F. Here's what I did
to accomodate the change but it doesn't work yet.

Changes I made (which won't work though):
1. Duplicated "Private Sub Worksheet_Change(ByVal Target As Range)" and
renamed "Target" to "TargetC" and "TargetD".
2. Since step #1 did not work, tried the same with "Range"... now "RangeC"
and "RangeD".

So, my questions a

How can C drive D... and how can D drive E & F (rather than "YesNo"... "New
York", etc & "Mike" etc.?

Thanks so much in advance,
Tom







"Frank Kabel" wrote in message
...
Hi Tom
glad it works for you and thanks for the feedback :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

This works just fabulously!!! THANK YOU SO MUCH!

--
Tom


"Frank Kabel" wrote in message
...
Hi Tom
not quite sure what should happen with lets say column E if you
enter
'No' in C2 but try the following (note: i hardcoded the list an do
not
use a named range as you only have two options with 'yes' and
'No'):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:


Currently, column D is dependent on column C.

First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".

Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".

2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.

and so on...

Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.


Any additonal feedback would really help me!!!

Thanks,
Tom




-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.

Would you mind having another look at the additional
information?
Thanks so
much in advance!!!

****************

Data input/selections are made on
worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined
function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out"
the
originally
stored
function in B1.



Thanks in advance,
Tom






.

Hi
not tested but try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
select case target.column
case 3
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
case 4
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="New
York,Miami,Los Angeles"
.offset(0,1).Validation.delete
.offset(0,1).Validation.add
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Mike,John,Richard,Tina"
end select
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

One more follow-up question...

currently, the "YesNo" in column C "drives the other "YesNos" of
column D,
E, F, G.

I tried to make a minor change... unsuccessfully though... let's say:

1. "YesNo" in C drives "YesNo" in D
2. If "No" in D, then "N/A" in E; if "Yes" in D then "New York,
Miami, Los
Angeles" in E.
3. If "No" in D, then "N/A" in F; if "Yes" in D then "Mike, John,
Richard,
Tina" in F.

P.S. There's no link between columns E & F... their values are
simply
driven by the "YesNo" selection in D.


Problem... currently, D does not allow to drive E or F. Here's what
I did
to accomodate the change but it doesn't work yet.

Changes I made (which won't work though):
1. Duplicated "Private Sub Worksheet_Change(ByVal Target As Range)"
and
renamed "Target" to "TargetC" and "TargetD".
2. Since step #1 did not work, tried the same with "Range"... now
"RangeC"
and "RangeD".

So, my questions a

How can C drive D... and how can D drive E & F (rather than
"YesNo"... "New
York", etc & "Mike" etc.?

Thanks so much in advance,
Tom







"Frank Kabel" wrote in message
...
Hi Tom
glad it works for you and thanks for the feedback :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

This works just fabulously!!! THANK YOU SO MUCH!

--
Tom


"Frank Kabel" wrote in message
...
Hi Tom
not quite sure what should happen with lets say column E if you
enter
'No' in C2 but try the following (note: i hardcoded the list an
do
not
use a named range as you only have two options with 'yes' and
'No'):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then
Exit
Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im
Newsbeitrag
...
Frank:


Currently, column D is dependent on column C.

First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".

Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".

2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.

and so on...

Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.


Any additonal feedback would really help me!!!

Thanks,
Tom




-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?

For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.


--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
Frank:

Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.

Would you mind having another look at the additional
information?
Thanks so
much in advance!!!

****************

Data input/selections are made on
worksheet: "Priorities"

For instance,...

C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200

D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200

E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200

****************


All of the range names reside on worksheet: "SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on


Thanks again. Your help is greatly appreciated!!!

Tom




"Frank Kabel" wrote in message
...
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom" schrieb im Newsbeitrag
...
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.

*****************************************

Does anyone know how to create a user-defined
function which will
allow me
to do the following?

1. From a drop-dop box (combo box), select value of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to "No".


At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)



PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.

Essentially, the Yes/No selection in B1 "wiped out"
the
originally
stored
function in B1.



Thanks in advance,
Tom






.