list all non blank cells

[adamsfarley]

I have a list of values that are a result of a formula and there are blank cells within the list. How can I create a new list that display it one after another combining the entire list.

I have tried this

Please Login or Register  to view this content.

Though I am pulling the data from another sheet and so it fails, even when I change it.

Thanks

[PeteABC123]

Try this:

This will remove blanks and move to the top all values in a range.
Enter as array formula. (CTRL+SHIFT+ENTER), not just enter. Enter into your desired cell, and copy down.


"breaks" is a named range. In your case, $d$2:$d$20. name the range.


=IF(ROWS($1:1)<=COUNTA(breaks),INDEX(breaks,SMALL(IF(breaks<>"",ROW(breaks)-MIN(ROW(breaks))+1),ROWS($1:1))),"")
Pete

[FDibbins]

Perhaps apply filters, and filter out all the blanks?

[adamsfarley]

It comes up with "#NAME?" is this field. The reason being is the range that I want combined have formulas in, so I am guessing that it's saying that these fields aren't blank even though nothing is being shown due to the formula that I have in there.

However, the code you gave does work when I copy values only into a new sheet.

Unless you know of a way that can resolve this?

[PeteABC123]

Try this, also an array formula: breaks is a named range. Enter it in W2.

Please Login or Register  to view this content.

Pete