Copy entire rows to different sheet when condition is met

[Riyaz Ahmed]

Hi,

i have a workbook wherein there are +/-15 sheets with different sheet names e.g SCHOOL, COLLEGE, UNIVERSITY ETC

Column A is for Serial No.
Column B is for Contact No.
Column C is for Name
Column D is for Status and so on

whenever i type "RECEIVED" in Column D, i want that entire row to be copied to a new sheet from each of the above sheets mentioned and the rest that is not mentioned as "RECEIVED" should be left in its place, also in the target sheet the serial number should in order like 1,2,3 etc.

Your expert help will be appreciated.
Thanks,

 

[rahulshewale1]

Hii,

this is you looking for ?

=IFERROR(INDEX(dATA!C$2:C$20,AGGREGATE(15,6,(ROW(dATA!$C$2:$C$20)-ROW(dATA!$C$2)+1)/(dATA!$D$2:$D$20="RECEIVED"),ROWS(N$2:$O2))),"")


thanks
rahul shewale

 

[Riyaz Ahmed]

Hii,

this is you looking for ?

=IFERROR(INDEX(dATA!C$2:C$20,AGGREGATE(15,6,(ROW(dATA!$C$2:$C$20)-ROW(dATA!$C$2)+1)/(dATA!$D$2:$D$20="RECEIVED"),ROWS(N$2:$O2))),"")


thanks
rahul shewale

Thanks Rahul for your help, it works gr8 as shown in your attached file.
riyaz.