Depenant validation list with dynamic ranges

[azureriders]

manifest.xlsx

The attached file has a list of employees on sheet2. There is then a check list area that defines certain tasks (Jump Types) that each employee is qualified for (this area will eventually work off a SelectionChange macro causing the cells to respond as check boxes). To the right of this is several columns that will be hidden and have the purpose of creating a dynamic range with a list of employees for each task. There is also a Jump Type list. This list has more that just the types performed by employees as we do have customers making jumps as well.

Now back on Sheet1. Column A is number 1-23 representing the slots available on the aircraft. Column B is for the type of Jump to be performed. Column C is a dependant drop down list of employees qualified to perform the jump type in Column C. Indirect is not liking the dynamic ranges I have used in the dependant lists.

I have found that the dynamic range of Jump types (Column B) is not a problem. However the dynamic ranges used for the depedant list are a problem. In the attached file I have changed the TAN_I list to a non dynamic range and the dependant list works fine, however I would REALLY like a dynamic list here. The check box area of Sheet2 will be filled out each morning to represent the number of employees on staff that day. The difference in Skydivers on hand from a Tuesday in December to a Saturday in August is huge, hince the need for the dynamic range.

Any help or ideas would be greatly appreciated.

[Fotis1991]

As i told you,yesterday in this thread,

http://www.excelforum.com/excel-form...cent-cell.html

INDIRECT does not like the Dynamic ranges.

Marcol, show a way to do this in this thread.

http://www.excelforum.com/excel-gene...own-lists.html

J.B shows another way here.

http://www.excelforum.com/excel-tips...t-problem.html

[azureriders]

Thanks Fotis. That was exactly what I was looking for. I had a work around that was working well but utilized some VBA code. I was really hoping to acomplish the task without VBA and your links took care of that.