Get all cells that are > zero dragged across

[nemo74]

Hi all quick and fast since I'm in a hurry :P

A	B	C	D	E
0	10	0	3	9

I need a formula that I can put in A3 and drag right that will show:

A	B	C	D	E
10	3	9

I can use

Please Login or Register  to view this content.

and get the 1st greater than zero, but I can't figure out something to drag to the right.



Thanks for the help!

[bebo021999]

Try this array formula:

Please Login or Register  to view this content.

Confirmed with Ctrl-shift-enter

[nemo74]

Hiya Bebo!

That formula works great on my example, but when I tried to change up some things I just get a blank. Can you point out what i'm doing wrong?

Please Login or Register  to view this content.

using Ctrl Shift Enter for an array.

Edit, so the column function is the reason its not working, at least I figured that out, not sure how to fix it, but I know what wrong :P

Edit #2
Fixed it, I had to change the index rage since coloumn counts the sheet not the range. Problem fixed. Thanks for in guidance Bebo, I have now learned a new trick and strangly enough a bit better understanding of an arrays!