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.

Client	Sales Rep	Status	Amount	Quantity
Client 2	Karen	Pending	$1,500	9
Client 3	Liam	Confirmed	$500	12
Client 4	Kim	Lost	$2,500	15
Client 5	Liam	Pending	$1,420	9
Client 6	Karen	Lost	$600	4
Client 7	Liam	Pending	$1,900	46
Client 8	Liam	Confirmed	$6,300	85
Client 9	Kim	Lost	$800	17
Client 10	Liam	Pending	$750	5

<tbody>
</tbody>

 

[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.