Formula that only imports checked rows

[twl2009]

Hi,

I have a workbook with 2 sheets. Sheet 2 brings certain values across from sheet 1 using a lot of different formulas. At the moment all of the rows from sheet 1 are imported across as the formulas all look for the same cell on sheet 1.

What I would really like to do is have sheet 2 only bring across the row if it has an "X" in column A and ignore all rows that dont, so that if I only "X" one row sheet 2 only has one row not all of them.

Does this make sense, is it possible?

Thanks

 

[mrhstn]

Something like...

Book1
	A	B	C
1	Check	Something 1	Something 2
2	x	3	735
3	x	6	928
4	x	10	385
Sheet2

Cell Formulas
Range	Formula
A2	{=IF(ROW(A1)>COUNTIF(Sheet1!$A:$A,"x"),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="x",ROW(Sheet1!A:A)),ROWS($A$1:A1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

You can them copy down as far as you need and across the same.

 

[twl2009]

This looks like exactly what I need, except for some reason it wont go in properly. I am using Control Shift Enter.

 

[mrhstn]

Copy the bit in between the {} curly braces...

=IF(ROW(A1)>COUNTIF(Sheet1!$A:$A,"x"),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="x",ROW(Sheet1!A:A)),ROWS($A$1:A1))))

Use ctrl+shift+enter not just enter.

I should have posted the sheet1 data in my first reply, it looks like

Book1
	A	B	C
1	Check	Something 1	Something 2
2		1	456
3		2	713
4	x	3	735
5		4	616
6		5	749
7	x	6	928
8		7	831
9		8	363
10		9	345
11	x	10	385
Sheet1

 

[twl2009]

Thank you mrhstn, that has been really helpful.