Data Validation Dropdowns to Default Position in Data

[Seeker After]

Hey All, How does one get the data validation drop down to default to a certain position in the data? I have studied it out a bit on the internet but did not find a good fit for my purpose. In the attached file I have a simple example showing a table of data with dates. Perhaps the twist that is causing me trouble from other examples is that the data validation dropdowns should default to the first and second dates in the column unless otherwise specified. The background, I have a dataset that is pasted in from an outside source. When it comes in, all of the previous data validation drop-down selections are made bogus causing visual havoc in the formulas for less experienced users. Looking for ideas. Thanks.

 

[Seeker After]

Here is the promised example file

 

[p45cal]

I don't think you can set which data validation option is the default!
What you can do is have a bit of code. This, in the RAW DATA sheet's code-module will check if something has changed in column C of that sheet, and if so set the values of those 2 cells in the Dropdown Test sheet to the first and second values of the list in column C:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns(3), Target) Is Nothing Then
  With Sheets("DropDown Test")
    .Range("C4") = Range("c3")
    .Range("C6") = Range("c4")
  End With
End If
End Sub

It worked here.
(To test it, you don't have to plonk new data into the RAW DATA sheet, you only have to change any cell in column C.)

 

[Seeker After]

Thanks for the response. I'm having a bit of difficulty getting it to run. I've pasted it into a new module created by right-clicking on the Raw Data worksheet in the tree. however, when I attempt to run it, it opens the macro window and stops. I am a real novice with VBA, any ideas what I missed?

 

[p45cal]

however, when I attempt to run it,

You don't have to run it yourself, it should be triggered by your changing any cell in column 3 (column C) of the RAW DATA sheet, just as putting new data in that sheet is likely to change at least one cell in column C.
Is it a manual paste to column C?

In the attached, I left the dropdowns in Cells C4 and C6 having selected values somewhere in the middle of the list. When you open the file, confirm this for yourself.
Next go to the RAW DATA sheet and delete the hi I've put in cell C30 (this'll count as changing something in column C). Nothing should appear to happen, but if you go back to the Dropdowns Test sheet and see which values the dropdowns have selected, I'd hope you'd find they're the first and second values in the list.

 

[Seeker After]

Thanks so much. I more wrinkle. I don't always know what column will contain the dates when the data comes in. Is there a way to determine column contains the dates then pull the dates from that?

 

[Seeker After]

One more as part of that above. In the live sheet I have the Raw Data but then a series of calculation sheets from which the majority of my data validation dropdowns take their dates. I noticed that although a change on the raw data sheet makes it to the calc sheet via formulas the code you put together doesn't pick it up and doesn't seem to make the desired changes to the dropdowns. Is there a way to watch for a change on raw data but direct the dropdowns to "default" to dates from the calc tab?

 

[p45cal]

See attached. Macro stays in RAW DATA sheet, with a change to the code.
To show it works, do as before and then edit RAW DATA cell C32.
Currently, it responds to changes in Column C only of RAW DATA but if you can tell me a column or a range on the RAW DATA sheet that will ALWAYS change when new data is pasted, tell me where that is and I'll tweak the code.