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