How to ignore blank cell in a range while using Data Validation

[Nicoloid]

I am using dependent data validation in cell ‘Sheet2!$B2’. It’s formula is ‘=INDIRECT($A2)’. The drop down 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)

[Koman]

Try the following : =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.

[Leanne]

Could you please confirm that I’m doing the right thing. I am 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 dependent data validation cell is ‘INDIRECT($A2) where ‘A2’ displays the name of the named range in question.