Copying Data to next available row in other sheet

[DubRed]

I would really appreciate someone's help with something I've been stuck on for the last day and a half.

In sheet1! i have three columns:
col A - Client Name
col B - Project Name
col C - Project Type

There are 3 project types - Design, Development and Marketing. All new sales are added one below the other in sheet1!

As a new sale is added in sheet 1 i would like the client name (col A) and Project Name (col B) to be copied to the next available row in sheet2! if the project type is development.

Anyone any ideas?

Thanks a mill

 

[NBVC]

Have a look at this.

Sheet 1: Data

Book1
	A	B	C	D
1	Company	Project Name
2	ABC	Development
3	DEF	M1
4	GHI	M2
5	JKL	J2
6	MNO	Development
7	PQR	M4
8	STU	Development
9	VWX	F1
Sheet1

Sheet 2: Results

Book1
	A	B	C	D
1	Company	Project Name
2	ABC	Development
3	MNO	Development
4	STU	Development
5
6
7
8
9
Sheet2

Fromula in A2:
=IF(ROW(Sheet1!$A2)-ROW(Sheet1!$A$1)>COUNTIF(Sheet1!$B$1:$B$9,"Development"),"",INDEX(Sheet1!$A$1:$A$9,SMALL(IF(Sheet1!$B$1:$B$9="Development",ROW(Sheet1!$A$1:$A$9)),ROW(Sheet1!$A2)-ROW(Sheet1!$A$1))))

Formula in B2:
=IF(ROW(Sheet1!$A2)-ROW(Sheet1!$A$1)>COUNTIF(Sheet1!$B$1:$B$9,"Development"),"",INDEX(Sheet1!$B$1:$B$9,SMALL(IF(Sheet1!$B$1:$B$9="Development",ROW(Sheet1!$A$1:$A$9)),ROW(Sheet1!$A2)-ROW(Sheet1!$A$1))))

These formulas are confirmed with CTRL+SHIFT+ENTER instead of just Enter.
Then copied down as far as you need. You will need to adjust the ranges to suit.

Note: The formula in B2 could simply be =If(A2<>"",Vlookup(A2,Sheet2!A2:B9,2),"") for a little more efficiency.

 

[DubRed]

thanks a million nbvc

that worked perfectly