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

[JackyJ]

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$100,-1),1)
Any help would be greatly appreciated.

I’m using Excel 2007, Vista

 

[jim may]

Would this help?
http://www.cpearson.com/excel/noblanks.htm

 

[RoryA]

Can you not remove the blank cells?

 

[JackyJ]

Thanks for the replies, guys.
Removing the blanks is not really an option. Also, I have trialled Jim’s idea, only using the following formula.
{=IF(SUM(IF(F$1:F$450<>"",1,0))>=ROW(),INDEX(F$1:F$450, SMALL(IF(TRIM(F$1:F$450)="", "", ROW(F$1:F$450)-MIN(ROW(F$1:F$450))+1), ROW(2:450))),"")}.
It works well, but for the drain on the cpu. With between 300 to 500 rows and 13 named ranges, the time taken to process any updates to the tables, is prohibitive.

This is why I’m looking for an alternative. I want to get rid of the named ranges with the array formulas and use the named range with the blanks to reference the dependant data validation.
Thanks again for your input.

 

[RoryA]

You could use:
=OFFSET(Sheet1!$L$1,MATCH("*",Sheet1!$L:$L,0)-1,0,COUNTA(Sheet1!$L:$L),1)
as the named range, but you will need to remove the INDIRECT formula, add another named range, called say GetList, using:
=EVALUATE($A2)
and then use =GetList in the DV dialog.
Note: since you are using a relative row number in a named range, make sure you have the correct row active when you define the name.