VBA Code to get unique and update Validation

[Sahil123]

Hi All,

Im hoping you can help me this.

I have a sheet called Index..On that sheet I have a table called RawData with a Date Range.

Each day This Date column gets filled in and that column will have duplicate dates in there as I update this column several times for each team.

I have a separate heading called DailyDates, WeeklyDates, MonthlyDates across G1:I1 and a data validation in J1.I have this data validation formula in J1...
=IF($B$1=1,DailyDates,IF($B$1=2,WeeklyDates,MonthlyDates))

What I want to do is get a unique list of dates for daily dates from the date range and then for the week get the Monday date for that week so that the week should always have a Mondays Date. In the Month Section it should have the unique month from the daily date range. Once this has been, I want to populate the data validation which should pick up the right named range..

I really hope this makes sense

This is what it should look like

Table names - RawData

Dates	Team
15/09/2016	Team1
15/09/2016	Team2
15/09/2016	Team3
15/09/2016	Team4
15/09/2016	Team5
16/09/2016	Team1
16/09/2016	Team2
16/09/2016	Team3
16/09/2016	Team4
16/09/2016	Team5
19/09/2016	Team1
19/09/2016	Team2
19/09/2016	Team3
19/09/2016	Team4
19/09/2016	Team5
20/09/2016	Team1
20/09/2016	Team2
20/09/2016	Team3
20/09/2016	Team4
20/09/2016	Team5

<tbody>
</tbody>

DailyDates	WeeklyDates	MonthlyDates
15/09/2016	12/09/2016	Sep 16
16/09/2016	19/09/2016
19/09/2016

<tbody>
</tbody>

The DataValidation should then pick up the correct named dynamic range depending on what option was selected

 

[Marcelo Branco]

Or

If dictWeek.Count Then
        .Range("L1").Value = 2
        .Range("J1").Value = Application.Max(dictWeek.keys)
End If

M.

 

[Sahil123]

Thank you - really appreciate what you did for me

thank you

 

[Marcelo Branco]

You are welcome. Glad to help.

M.