Easy Way to Copy Validation List to Cells

[JimmyC]

I am struggling with a what is probably, my design flaw (using Excel 2010). I am using rows 403-407, starting in Column R as a data validation list for quiz question answers in Column B. So for example, in cell B5, I have a data validation list from the range P403-P407. In cell B6, I have a validation list from range Q403-Q407--cell B7 the validation list from range R403-R407, etc. When I copy the validation list say from cell B5 to B6, I then have to manually adjust the data validation range.

Is there any way to copy the data validation list downward in Column B that moves the data validation list cells one column to the right--but maintains the rows at 403-407? I have almost two thousand cells to populate for a training quiz and I can't seem to find a way to avoid the manual adjustment to each data validation list after copying it. Thanks for any advice and the advice/answer maybe its too late to help me in this workbook as I have made a fatal design flaw---but at least I would know the next time to do this type of a project a different and correct way. THANKS!! JimC

[rory]

You can use formulas in DV, so something like this for B5:
=INDEX($P$403:$XFD$407,0,ROW()-ROW($B$4))
and then copy and paste the DV down.

[Rudi]

I see Rory already posted, but here is my untested (theoretical) solution....

You could try using INDIRECT.

Insert a column next to column B
In the new blank cell B5 type the letter P
In the new blank cell B6 type the letter Q
In the new blank cell B7 type the letter R
etc...

In your C/F, replace the fixed range P403:P407 to use the INDIRECT function.
For example: =INDIRECT($B5&"$403:"&$B5&"$407")
Then autofill down.

[JimmyC]

Rory, Rudi--many thanks! Your solution(s) will save me twenty hours at least! Take care. JimC