Data Validation Ignore Blanks of Range populated by Formula

Question

I have a named range that I'm using to populate a drop-down list. The cells in this range are populated by pulling unique values from another much larger named range on the spreadsheet. When populating this drop-down list, I would like to ignore blank cells in the first named range. I've tried using OFFSET, such as the following:

=OFFSET(Sheet1!$F:$F,1,0,COUNTA(Sheet1!$F:$F)-1,1)

This isn't working, and I think it's because Excel doesn't consider the cells to actually be blank. The cell is instead filled with a formula and the formula is returning "". Is there any way you guys know of to ignore blank values returned by formulas in drop-down lists?

Answer 1

You have to use =ROWS(F:F)-COUNTBLANK(F:F) to skip over blank formula results. The whole formula shoudl be something like:

 =OFFSET($E$1,
 COUNTA($E:$E)-(ROWS($E:$E)-COUNTBLANK($E:$E)),
 0,
 ROWS($E:$E)-COUNTBLANK($E:$E))`.

Tested: