return specific data without spaces

[CreativeRova]

Hi,

I have a spreadsheet like the below, that has a long list of clients from sales agents and their current status. ie confirmed pending lost.

This spreadsheet is updated constantly with status new deals and amounts.

I want a spreadsheet for each Sales Rep that references this master sheet which only shows their numbers.

I can currently do this by =if(a2="Karen",a1,"") but the problem is that when it does not =Karen it leaves that row blank. I want a list of confirmed deals for Karen, a separate list of lost and one for pending all condensed as to not leave gaps on the sales agents own spreadsheet.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Sales Rep[/TD]
[TD]Status[/TD]
[TD]Amount[/TD]
[TD]Quantity[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Karen[/TD]
[TD]Pending[/TD]
[TD]$1,500[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Liam[/TD]
[TD]Confirmed[/TD]
[TD]$500[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 4[/TD]
[TD]Kim[/TD]
[TD]Lost[/TD]
[TD]$2,500[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 5[/TD]
[TD]Liam[/TD]
[TD]Pending[/TD]
[TD]$1,420[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 6[/TD]
[TD]Karen[/TD]
[TD]Lost[/TD]
[TD]$600[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 7[/TD]
[TD]Liam[/TD]
[TD]Pending[/TD]
[TD]$1,900[/TD]
[TD]46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 8[/TD]
[TD]Liam[/TD]
[TD]Confirmed[/TD]
[TD]$6,300[/TD]
[TD]85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 9[/TD]
[TD]Kim[/TD]
[TD]Lost[/TD]
[TD]$800[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 10[/TD]
[TD]Liam[/TD]
[TD]Pending[/TD]
[TD]$750[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Ok, Your version works perfectly for what I need. My issue is I can not find where you are referencing the master page, meaning I don't know what to change to make it work on my spreadsheet. [...]

See Formulas | Name Manager. All of the range definitions are there. And the formulas show you how these ranges (from master) are referenced.

 

[CreativeRova]

Sorry Aladin, this still leaves me in the same position I do not know where your referencing. I have tried to move each equation to do it 1 by 1 and none of the worked.

If your done explaining cause you have explained best you could no worries, I just don't understand what you are doing. Your formulas are more than I know.