Ignore blank cells in a named range when using dependent data validation
 

I am using dependant data validation in cell ‘Sheet2!$B2’. It’s formula is ‘=INDIRECT($A2)’.
The dropdown menu in ‘Sheet2!$B2’ then references a named range in ‘Sheet1!$L:$L’, which at times has one or more blank cells at the top of the column. I would like to ignore the blank cells in the column so that only the populated cells in the named range appear in the drop down menu.
I acquired the following formula from the net to replace ‘Sheet1!$L:$L’ in the name manager, but I am unable to make it work.
=OFFSET(Sheet1!$L$2,0,0,MATCH("*",Sheet1!$L$2:$L$1 00,-1),1)
Any help would be greatly appreciated.

I'm using Excel 2007, Vista

Cracked it!

=OFFSET(Sheet1!$L$2,COUNTBLANK(OFFSET(Sheet1!$L$2, 0,0,COUNTA(Sheet1!$L:$L),1)),0,COUNTA(Sheet1!$L:$L )-1,1)

Any blank cells inside the non-blank range will be counted because of the COUNTBLANK function so the starting reference will go down further.

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