function??

[jchapman_ps]

How can I dispaly all of the rows from a worksheet in a seperate worksheet based on a cell value from a third worksheet.

Ex:

Worksheet 1
a,1,1,1
a,2,2,2
a,3,3,3
b,1,1,1
b,2,2,2
b,3,3,3

Worksheet 2
b,1,1,1
b,2,2,2
b,3,3,3

Worksheet 3
b

Obviously Excel is not one of my strenghts so feel free to dumb it down.

Thanks for you help,
Josh

[rylo]

Josh

If your worksheet1 data is in the sheet1!A1:C6, and your worksheet3 data is in sheet3!A1 then in sheet2
A1: =IF(ROW()>COUNTIF(Sheet1!$A$1:$A$6,Sheet3!$A$1),"",Sheet3!$A$1)
The following formula is array entered (ctrl, shift, enter)
B1: =IF(ROW()>COUNTIF(Sheet1!$A$1:$A$6,Sheet3!$A$1),"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$1:$A$6=Sheet3!$A$1,ROW(Sheet1!$A$1:$A$6)),ROW())))

Copy from B1 to C1 and D1.

Then copy A1:D1 down as required.

HTH

rylo

[Teethless mama]

How can I dispaly all of the rows from a worksheet in a seperate worksheet based on a cell value from a third worksheet.

Ex:

Worksheet 1
a,1,1,1
a,2,2,2
a,3,3,3
b,1,1,1
b,2,2,2
b,3,3,3

Worksheet 2
b,1,1,1
b,2,2,2
b,3,3,3

Worksheet 3
b

Obviously Excel is not one of my strenghts so feel free to dumb it down.

Thanks for you help,
Josh

Try this:

"DATA" is a define name range in Sheet 1
Sheet3 A1 hold the criteria

In sheet 2
=IF(ISERR(SMALL(IF(LEFT(DATA,1)=Sheet3!$A$1,ROW(INDIRECT("1:"&ROWS(DATA)))),ROWS($1:1))),"",INDEX(DATA,SMALL(IF(LEFT(DATA,1)=Sheet3!$A$1,ROW(INDIRECT("1:"&ROWS(DATA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

[jchapman_ps]

I am using:

Column1:
=IF(ROW()>COUNTIF(OUTPUT_BY_PM!A2:A100,Sheet1!$A$1),"",Sheet1!$A$1)

Column2:
=IF(ROW()>COUNTIF(OUTPUT_BY_PM!A2:A100,Sheet1!$A$1),"",INDEX(OUTPUT_BY_PM!B:B,SMALL(IF(OUTPUT_BY_PM!$A$2:$A$100=Sheet1!$A$1,ROW(OUTPUT_BY_PM!$A$2:$A$100)),ROW())))

It is working but I am having some difficulty inserting coulmn header rows???

Thanks again for your assistance.
Josh

[jchapman_ps]

If I was clear. The function is working but when I insert 2 rows above for column headers, the function now pulls the the third row instead of the first.

[rylo]

Hi

You will have to adjust the row() to be row()-n where n is the row number of the first line of data minus 1.

So if the first data row is 4, then it will be row()-3. If the first datarow is 10, then it will be row()-9.


HTH

rylo