Remove Nulls and Blank Formulas from List

[philchi]

Hello,

I'm trying to return a list of values from a column but exclude those that are Blank and those which have a formula where the results are an empty string (""). I've tried several variations of example from the web but none of them seem to work properly on the formula rows. Unfortunately the new Excel array functions haven't been deployed to the semi-annual release cycle so I can't use those just yet.

My latest formula is below, but I've tried a few variations of IFERROR(INDEX,SMALL, etc. Thoughts?

Please Login or Register  to view this content.

Thanks

[KOKOSEK]

Into D4:

Please Login or Register  to view this content.

as is array formula, it have to be accept with Ctrl+Shift+Enter.

[philchi]

Yes, that works - though seems like I've written a bad question. I assumed once I removed the blanks from the source list I could just reference the result in a drop-down list. Turns out, the drop-down list has all of the blanks from the new formula.

This kind of stuff should just be built into Excel; shouldn't need multiple arrays to accomplish a common and simple task.

Anyway, apologies for the incomplete question, let me try to figure out how to fix this and remove the blanks from the drop down list.

[JeteMc]

Perhaps the following will help:
1. Put the following into cell D2 and down: =IFERROR(INDEX(B$2:B$20,AGGREGATE(15,6,(ROW(B$2:B$20)-ROW(B$1))/(B$2:B$20<>""),ROWS(A$1:A1))),"")
2. Use the following as the "Source" for the data validation list (modeled in cell I2): =OFFSET(D$2,0,0,SUMPRODUCT(--(D$2:D$20<>"")),1)
Let us know if you have any questions.