Lookup for one value and then copy the whole role.

[JDJong]

Hi everyone,

there is a large amount of orders I receive everyday which Needed to be recorded into the master file, in contains of different category of course types.

Is there a way that could look up the regular courses and copy the whole row down, and whenever new courses are registered, the formula can automatically copy down so that i could have an overview of the Status of the monthly order.

Please any assistance is greatly appreciated.
Sheet 1 is master file and sheet 2 is the result i would like to have:
[TABLE="width: 263"]
<tbody>[TR]
[TD="colspan: 2"]Sheet 2 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]Course No.[/TD]
[TD]Subject[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]4901[/TD]
[TD]Math[/TD]
[TD="align: right"]9800[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]4902[/TD]
[TD]Math[/TD]
[TD="align: right"]8510[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181011[/TD]
[TD]Math[/TD]
[TD="align: right"]7220[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181015[/TD]
[TD]Math[/TD]
[TD="align: right"]5930[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]1475856[/TD]
[TD]Science[/TD]
[TD="align: right"]4640[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]5565201[/TD]
[TD]Science[/TD]
[TD="align: right"]3350[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]985859[/TD]
[TD]Science[/TD]
[TD="align: right"]2060[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]6521[/TD]
[TD]Science[/TD]
[TD="align: right"]770[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]9852[/TD]
[TD]Science[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]8522[/TD]
[TD]Literature[/TD]
[TD="align: right"]651[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181012[/TD]
[TD]IT[/TD]
[TD="align: right"]5930[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181013[/TD]
[TD]Review[/TD]
[TD="align: right"]4640[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181014[/TD]
[TD]Science[/TD]
[TD="align: right"]3350[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181015[/TD]
[TD]Science[/TD]
[TD="align: right"]9800[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181016[/TD]
[TD]Science[/TD]
[TD="align: right"]8510[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181017[/TD]
[TD]Science[/TD]
[TD="align: right"]7220[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181018[/TD]
[TD]Science[/TD]
[TD="align: right"]7220[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]985[/TD]
[TD]Science[/TD]
[TD="align: right"]3350[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]552[/TD]
[TD]Science[/TD]
[TD="align: right"]16000[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

[TABLE="width: 380"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Course No.[/TD]
[TD]Contact person[/TD]
[TD]Subject[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]4901[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD="align: right"]9800[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]4902[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD="align: right"]8510[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181011[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD="align: right"]7220[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181015[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD="align: right"]5930[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]1475856[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]4640[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]5565201[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]3350[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]985859[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]2060[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]6521[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]770[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]9852[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]8522[/TD]
[TD][/TD]
[TD]Literature[/TD]
[TD="align: right"]651[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181012[/TD]
[TD][/TD]
[TD]IT[/TD]
[TD="align: right"]5930[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181013[/TD]
[TD][/TD]
[TD]Review[/TD]
[TD="align: right"]4640[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181014[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]3350[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181015[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]9800[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181016[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]8510[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181017[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]7220[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]20181018[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]7220[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD="align: right"]562[/TD]
[TD][/TD]
[TD]PE[/TD]
[TD="align: right"]5930[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD="align: right"]956[/TD]
[TD][/TD]
[TD]ART[/TD]
[TD="align: right"]4640[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]985[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]3350[/TD]
[/TR]
[TR]
[TD]regular [/TD]
[TD="align: right"]552[/TD]
[TD][/TD]
[TD]Science[/TD]
[TD="align: right"]16000[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[AlanY]

you know that can be achieved easily by putting a filter in the top row of the master file

 

[JDJong]

Yes that's true Alan, in a latter column G
there are 58 cost Centers
and i would like to make an overview of each Center.

perhaps you have an idea of how it would work ?

Thank you

 

[JDJong]

And if I filter the regular ones and in sheet 2 row B2 =sheet1!B2 copy down, all the entries of the other courses are also copied down.

I think it Needs to be excluded the other's first?