EXCEL question

[jimhelm]

I have a spreadsheet project I'm working on. lemme splain it to you

ok, so the main sheet, which I want the sales manglers to enter data into has a row of fields for them to fill in details about each deal: salesman, team, new/used, etc...

Easy enough to build an if/then formula and separate stats based on the answer in a specific field. Like, if I want new car gross only, or if I want all of team 1's deals on this sheet... I can do that. maybe it's not the most efficient way though... here's what I do:

I made the 'master sheet' where all deals are logged. this has a column for team #
then I made 4 identical copies of that sheet
I named those copy team 1-4
say the team gets logged in column B of the master sheet

so on the team1 sheet I set a formula that goes like this:
(in cell A4)

=IF(Master!B4=1,Master!a4,"")

and stretched that over the whole sheet.

that gives me rows copied from the master sheet ONLY if the answer to TEAM = 1.

the problem is that there are lots of empty lines. I'd like for all of the team one deals to stack up at the top.

anyone know how to do that?