If no value, jump to next one

[viktor_david]

Hi,

I want to create a formula that brings up a certain value only if this is there, if not, jump to next cell.
Basically like this:
A shows the return values for a customer
Formula shows only the returns for this customer. (assume it is in another workbook)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]-5.5[/TD]
[TD]-5.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


PLease Help

 

[Special-K99]

in B1
=IFERROR(INDEX($A$1:$A$1000,SMALL(IF(($A$1:$A$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter
and copy down column B

 

[Scott T]

If you have no headers
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ROWS(A$1:A1)<=COUNTIF($A$1:$A$9,"<>0"),INDEX($A$1:$A$9,SMALL(IF($A$1:$A$9<>0,ROW($A$1:$A$9)),ROWS($A$1:A1))),"")

If you have headers in row one. Also must be entered with CONTROL+SHIFT+ENTER

=IF(ROWS(A$2:A2)<=COUNTIF($A$2:$A$10,"<>0"),INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>0,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($A$2:A2))),"")

 

[viktor_david]

If you have no headers
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ROWS(A$1:A1)<=COUNTIF($A$1:$A$9,"<>0"),INDEX($A$1:$A$9,SMALL(IF($A$1:$A$9<>0,ROW($A$1:$A$9)),ROWS($A$1:A1))),"")

If you have headers in row one. Also must be entered with CONTROL+SHIFT+ENTER

=IF(ROWS(A$2:A2)<=COUNTIF($A$2:$A$10,"<>0"),INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>0,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($A$2:A2))),"")

This is perfect answer... Same as above.
Now, I am getting those numbers from another worksheet, instead of A1:A9 it'll be Trades!A1:A9, for example...

When i plug this formula, it only takes a few numbers, not all of them.. what can I do now?

 

[viktor_david]

in B1
=IFERROR(INDEX($A$1:$A$1000,SMALL(IF(($A$1:$A$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter
and copy down column B

Thanks a lot for your answer. It works perfectly.

Please, see my answer above for the following challenge, please

THanks a lot

 

[DrunkenPanda]

if the "-" are blank cells, then this can help:
Eliminating Blank Cells In A Range

 

[Special-K99]

Try

=IFERROR(INDEX(Trades!$A$1:$A$1000,SMALL(IF((Trades!$A$1:$A$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),1),"")

To be honest the description of your problem should describe the problem and data EXACTLY AS IT IS.
You shouldn't post a problem saying "Here's my problem and this is my data" then several posts later "Well actually, my data is a bit different to that"
It means we have to spend more time on a solution that works AS YOU ORIGINALLY EXPLAINED IT.

 

[viktor_david]

Try

=IFERROR(INDEX(Trades!$A$1:$A$1000,SMALL(IF((Trades!$A$1:$A$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),1),"")

To be honest the description of your problem should describe the problem and data EXACTLY AS IT IS.
You shouldn't post a problem saying "Here's my problem and this is my data" then several posts later "Well actually, my data is a bit different to that"
It means we have to spend more time on a solution that works AS YOU ORIGINALLY EXPLAINED IT.

Yes, guys. you are right. I am sorry for that
I am very thankful for your input, all of you.
I did manage to fix it and it all looks fine now.
I will contact you if anything else but this is a f-ing nice site with wise ppl.

Thanks