Conditional copy to list

[ArtZ]

Hi,

I need to form a list of text (column A) and corresponding numeric values (column B) whereby the list values are placed in the next unused cell in these columns when the worksheet is updated. Placing values in the list is based on finding a TRUE value in column E.

The logic for this goes as follows:

If the value in cells E44:E1445 is true then copy the corresponding text value from the range C44:C1445 into the next unused cell in column A starting at cell A44.

and

if value in cells E44:E1445 is true then copy the corresponding numeric value from the range E44:E1445 into the next unused cell in column B starting
at cell B44.

Can anyone in the forum help with this logic?

Thanks,

Art

[NBVC]

Try:

=IF(ROWS($A$1:$A1)>COUNTIF($E$44:$E$1445,TRUE),"",INDEX($C$44:$C$1445,SMALL(IF($E$44:$E$1445=TRUE,ROW($E$44:$E$1445)-ROW($E$44)+1),ROWS($A$1:$A1))))

Which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then copy down as far as you want.

To extract other column info...just replace the $C$44:$C$1445 after the INDEX function to the column range you want and confirm the formula with teh CSE key combination before copying it down.

[ArtZ]

Hi NBVC,

Thanks for your reply. I see that I made a goof:

If the value in cells E44:E1445 is true then copy the corresponding text value from the range C44:C1445 into the next unused cell in column A starting at cell A44 is incorrect and should have been:

If the value in cells I44:I1445 is true then copy the corresponding text value from the range C44:C1445 into the next unused cell in column A starting at cell A44.

I tried making this change in your cell formula and got #NUM! returned when I changed the Es to Is.

Any suggestions?

Thanks,

Art

[NBVC]

After you change the E's to I's, you need to hold the CTRL and SHIFT keys down and then press ENTER. You will notice { } brackets appear around the formula.... Then you can copy it down. This is a special "array formula".

Array formulas: http://www.cpearson.com/excel/ArrayFormulas.aspx

[ArtZ]

I have the array formula entered, however, now #N/A is returned in all cells with the array formula.

Art

[NBVC]

Do you have any errors in any of those cells?

Maybe you could post the workbook showing the problem.

[ArtZ]

Good Suggestion. Attached is the worksheet. There's a lot going on and the end goal is to modify the worksheet to place the stocks that meet the conditional requirements in column I into the list box when the button "Display Stocks to buy" is selected.

I did not create the worksheet and don't see how to do that, so for now, listing the stocks in column A will have to do.

Thanks for your help.

Art

[NBVC]

Like I said.. it's the N/A errors in I44:I45 causing the #N/A error in the formula result.

Get rid of the N/A in I44:I45 then select cell A44. Hit F2 to activate it and hold the CTRL+SHIFT keys down and hit ENTER... then copy down the formula.

[ArtZ]

Excellent! Got it. Thanks for your help.

Art

[NBVC]

Great!

Can you now please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save