If B2 = 1 then pull data into sheet 2, if B2 is blank skip to next 1

Hi guys,

I've got 2 sheets, the first has a list of all our products and the user puts a 1 in column B if they want said product. The second sheet should then just bring in the lines that the user selected.

What I'm trying to get is some form of formula that will look in column B and when it equals 1 to then bring in column A, if B doesn't equal 1 then it skips on to the next row until it finds a 1.


I've attached an example to try to illistrate what I'm looking for.

Thanks in advance


example 30-04-13.xlsx

hi djauncey, try this array formula:

Formula:

=IFERROR(INDEX('Product Selection'!$A$2:$A$8,SMALL(IF('Product Selection'!$B$2:$B$8=1,ROW('Product Selection'!$B$2:$B$8)-ROW('Product Selection'!$B$2)+1),ROWS(A$2:A2))),"")

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again. extend the range if needed

Try in A2 and copy down this ARRAY (see my Signature)formula.

=IFERROR(INDEX('Product Selection'!$A$2:$A$100,SMALL(IF('Product Selection'!$B$2:$B$100=1,ROW('Product Selection'!$A$2:$A$100)-1),ROW('Product Selection'!A1))),"")

This array formula** entered in A2 and copied down until you get blanks:

=IFERROR(INDEX('Product Selection'!A:A,SMALL(IF('Product Selection'!B$2:B$8=1,ROW('Product Selection'!B$2:B$8)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Excellent thank you both for your quick replies, works like a charm