Thanks for the reply.

Could you please confirm that I’m doing the right thing?
I’m using Excel 2007. I hit ctrl-F3 to bring up the Name Manager. I select the named range in question, hit edit and enter
=OFFSET(Sheet1!$L$2,COUNTBLANK(OFFSET(Sheet1!$L$2, 0,0,COUNTA(Sheet1!$L:$L),1)),0,COUNTA(Sheet1!$L:$L )-1,1)
into the ‘Refer To:’ box. This also removes the name of this range from the name box.
After doing the above, the drop down menu does nothing. It displays the arrow to the right hand side of the cell, but that’s it. The formula in the dependant data validation cell is ‘INDIRECT($A2) where ‘A2’ displays the name of the named range in question.

Thanks again