Automate assigned Data Validations

[emergguy]

I am so impressed with the responses I thought I'd try another puzzle I haven't been able to solve.

In short, the sheet is designed to manually populate a schedule.
For each day, there are various manpower availabilities - and the available names are in named ranges (ie _Jan1, _Jan2 etc) : (also shown with colours for ease of explanation).
I have manually prepopulated the Data Validations for Row 3 and Row 4, pointing each Data Validation range to its corresponding Named Range.

The question:
Since The schedule is filled for 3-4 months at a time, manually assigning the Data Validation range to each row(date) is time-consuming (and prone to error when keystroke fatigue sets in)
Is it possible to automate this - either by formula or a VBA script? (I am very new, but aim to dissect formulas and scripts when I can so I can learn more)

Much appreciated if anyone thinks this possible.

Book2.xlsx
	A	B	C	D	E
1
2	Day	Shift 1	Shift 2	Shift 3
3	1-Jan
4	2-Jan
5	3-Jan
6	4-Jan
7	5-Jan
8
9	Availabilities
10	_Jan1	_Jan2	_Jan3	_Jan4	_Jan5
11
12	Doc A	Doc A	Doc C	Doc A	Doc A
13	Doc B	Doc C	Doc D	Doc E	Doc B
14	Doc C	Doc D	Doc E	Doc F	Doc C
15	Doc D	Doc E	Doc F		Doc D
16	Doc E				Doc E
17	Doc F
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B3:D3	List	=_Jan1
B4:D4	List	=_Jan2

 

[Akuini]

The question:
Since The schedule is filled for 3-4 months at a time, manually assigning the Data Validation range to each row(date) is time-consuming (and prone to error when keystroke fatigue sets in)
Is it possible to automate this - either by formula or a VBA script?

Where is "availabilities" actually located? Is it in the same sheet (i.e. Sheet1)?

 

[emergguy]

Where is "availabilities" actually located? Is it in the same sheet (i.e. Sheet1)?

My apologies Akuini, I can see how the defined Named Ranges are unclear in my post.

Availabilities for each day are the Named Ranges ie _Jan1 = A12-A17, _Jan2 = B12:15
So
Jan1, the DataValidations for B3:D3 should be A12:A17
Jan2, the DataValidations for B4:D4 should be B12:B15

Thanks for your interest,

 

[Akuini]

I assumed that the named ranges already exist.
Try this:

Sub emergguy_1()
Dim c As Range, i As Long
For Each c In Range("B3:B7")
    With c.Resize(, 3).Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=" & Range("A10").Offset(, i).Value
        i = i + 1
    End With
Next
End Sub

 

[emergguy]

I assumed that the named ranges already exist.
Try this:

Sub emergguy_1()
Dim c As Range, i As Long
For Each c In Range("B3:B7")
    With c.Resize(, 3).Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=" & Range("A10").Offset(, i).Value
        i = i + 1
    End With
Next
End Sub

Perfect! Works like a charm.
Off to dissect the code and learn some more.
Much appreciated!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.