Conditional defalt on data validation list

Question

EDIT No. 2

*Ok, my previous articulations of my problem were, as pointed out, pretty rubbish. This edit, rephrases the question in a more straightforward manner, summarises the context and provides sample data.

SAMPLE DATA

Col 1   Col 2 RESET? UNIQUE VALUES
A       A     yes    A
C       C            B
D       D            C
A       A            D
B       B
A       A
D       D
C       C

Note, in order to replicate my problem, the list validation tool must be used to create a drop down list for each cell in Col 2, where the list items are A, B, C, D. Note, Only Col 1 and Col 2 (+ drop down lists) are part of the problem.

The RESET? cell is part of potential solution/how I would ideally like the solution to look.

The unique values column, is simply for convenience when creating the drop down lists. (See formula in the attempts section.)

DESCRIPTION

I have two columns, Col 1 & Col 2. As can be seen from the sample above, both are initially identical. However each cell in Col 2 has a drop down list, which is a list of the unique values in Col 1.

So far so easy. The problem is in reality my columns are much longer than my sample, so after playing around with Col 2, I may want to restore/reset it back to its initial values (ie to return it to its initial values, equal to Col 1).

QUESTION

I need a button that "resets" a column containing drop down lists so that its values matches those in another column.

CONTEXT

In my application Col 1 is for reference, while Col 2 is a counter factual column where a user can change a value to any of the four possible values (again, A, B, C or D in this example), to see what effect this has on other columns.

After playing around with Col 2, the user will want to reset Col 2 to its default values, which are those contained in Col 1. In other words, the default value of Col 2 is conditional on Col 1.

DIFFICULTIES & ATTEMPTS

The main difficulty seems to be with trying to apply both a formula and a drop down list to one cell.

I have tried using an IF formula in the cell, but this deletes the drop down list

I have also tried using the following formula in the source of the list validation set up box

Formula on cell B2*:

=IF(C2="yes",A2,D2:D5)

*where columns have an intuitive correspondence with the sample data above; i.e. Col 1 is the same as column A; Column D is UNIQUE VALUES).

But this merely restricts the option available in the list. If I could get this value to populate the cell without being manually clicked on, then this would be a perfect solution. (In my first edit, below, I used the INDEX function to try achieve this, but it did not work.)

I have not tried any VBA approaches as I'm not familiar with the language, but I am willing to try, if it seems fruitful.

NOTES & ERRATA

Please forgive if I have abused terminology in terms of drop down lists vs list validation. Hopefully they are interchangeable.

---

ORIGINAL QUESTION

I have two columns, X_1 & X_2. Imagine X_1 has values that I observe in reality - containing, say four unique values, over N observations. X_2 is a counter factual variable, which means I allow the user to change X_2 to any of the four values of X_1, via a drop down list. This has downstream consequences in terms of a function that I use to predict some Y.

The problem is that N is a very large number, so it would take too long to reset X_2 by hand.

What I'm looking for then is a button that can reset the list in X_2 according to the corressponding value in X_1.

EDIT:

As the screen shot shows, when the reset cell (ie D2) == "yes", the formula below restricts the drop down list to just one value; that which corresponds to X_1; otherwise it has all four unique values of X_1, which is contained in a list in G2:G5.

This is almost what I want, but the problem is that the user still has to select the restricted value. I'm currently looking for a way to populate the cell with the lis's first value (hence the INDEX function), but so far I have been unsuccessful.

Formula:

=IF($D$2="yes";INDEX($A$2;1);$G$2:$G$5)

The list in the selected cell only contains "A" because RESET is set to "yes"; otherwise the list would contain A,B,C,D. The problem is "A" does not populate automatically.

Answer 1

The solution, it turned out, was rather simple.

Private Sub CommandButton1_Click()
Range("B:B").Value = Range("A:A").Value
End Sub

Where A and B correspond to X_1 and X_2 in the question, respestively. The RESET "button" which I had included can be taken care of by the CommandButton, which I didn't know existed.

To be explicit about what this does:

• B is a column with drop down lists I can play around with B to my heart's content
• When I'm finished I can press my button and hey presto! All values of B return to their default values, which is equal to A.

My question was clearly far too complicated for such a simple task, but to be honest I've been massively taken off guard by how the default functionality of the CommandButton is exactly that which I need (In particular that it doesn't remove/delete my drop down lists once it's been used). Maybe this is the beauty of returning to software which has been designed to minimim entry costs for new users!