Validation List Dependencies

[Tom]

I need some help with defined ranges and dependencies between them.


BACKGROUND:
I have 2 worksheets (Input, SourceData).

The "SourceData" worksheet contains defined ranges.

I have a range called "YesNo-A" which refers to "=Input!$A$2:$A$50".

On the SourceData worksheet, I then use a drop-down menu (Validation | List)
in cells
A2:A50. The source for the list is "=YesNo-A".

Obviously, in cells A2:A50, the user then will be given to select either
"Yes" or "No".


SCENARIO:

1. If the user selects "No" in cell A2, I then want to populate B2
automatically with "No" as well.
2. If the user selects, however, "Yes" in cell A2, I want to allow the user
to select "Yes" or "No" from another range ("YesNo-B") dropdown menu.

Currently, cell B2 contains the formula below:
=IF(A2="No","No",YesNo-B)


PROBLEM:
I get the proper value of "No" in B2 if "No" has been selected in A2.
I also get the option to select "Yes" or "No" again if the value is "Yes" in
A2.

However, once "Yes" was selected in "A2" (and whatever the answer is in B2)
I CANNOT change
A2 to "No" again without autatically updating the value in B2 to "No".

Essentially, the formula "=IF(A2="No","No",YesNo-B)" in B2 gets "wiped out"
once a "Yes" or "No" selection has been made in B2.


Is there another way to solve this problem? Again, I'm trying to build
dependencies and sub-dependencies between "Yes" and "No" answers and I must
be able to change them while updating subsequent underlying dependencies.

I also need to be able to drill down even further for the Yes/No selection
between column B and C.

Makes sense?!?!


Tom

 

[Frank Kabel]

Hi Tom
as a cell can only hold a formula or a value no chance (IMHO) to
achieve this without using VBA and an event macro (e.g.
worksheet_change)

 

[Mike R.]

Frank:

Ok, that makes sense.... any suggestions as to how the macro should be
written?

Tom