ColA, ColB, ColC, and ColD are defined name ranges

In Sheet 2

B2:
=IF(ISERR(SMALL(IF(ColA=$A$2,ROW(INDIRECT("1:"&ROW S(ColA)))),ROWS($1:1))),"",INDEX(ColB,SMALL(IF(Col A=$A$2,ROW(INDIRECT("1:"&ROWS(ColA)))),ROWS($1:1)) ))

ctrl+shift+enter, not just enter
copy across from B2 to D2
In C2: change ColB to ColC (cse)
In D2: change ColB to ColD (cse)
select B2:D2 and copy down as far as needed


"Robert59" wrote:

I have a report that has in column A codes for different facilities. I would
like to create worksheet in the workbook that copy the contents of the rows
from the master worksheet depending on the contents of the cell in Column A.
For example
A B C D
SFMC 17 22 44
MAMC 12 13 37
SFMC 19 30 70

From this report on worsheet 1 would like to have a formula in worksheet 2
that will copy the contents of any row in worksheet 1 that has SFMC in column
A.
I have tried the IF statement but I can only get it to copy the contents of
column A not the entire row.
thanks for any advice!
Bob