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]

Are you wanting to create a sublist of records for a given rep with status either lost or pending?

 

[CreativeRova]

yes, it will be updated everytime they open the spreadsheet from the master worksheet. they do not have access directly to the master only their personal version.

I would like the status separated for sales reports. I also know I can move all in then apply filter to remove blanks but wondering is there another neater way.

 

[Aladin Akyurek]

master

Row\Col	D​
2​	$1,500
3​	$500
4​	$2,500
5​	$1,420
6​	$600
7​	$1,900
8​	$6,300
9​	$800
10​	$750

A2:A10 is defined as Client.
B2:B10 is defined as Rep.
C2:C10 is defined as Status.
D2:D10 is defined as Amount.
E2:E10 is defined as Quantity.
And Ivec is defined in the Name Manager as:

=ROW(Rep)-ROW(INDEX(Rep,1,1))+1

Karen (the sheet for the rep Karen)

Row\Col	A​	B​	C​	D​	E​
1​	karen
2​
3​	lost
4​	pending
5​
6​	2
7​	Idx	Client	Status	Amount	Quantity
8​	1	Client 2	Pending	1500	9
9​	5	Client 6	Lost	600	4
10​

In A6 control+shift+enter, not just enter:

=SUM(COUNTIFS(Rep,A1,Status,A3:A4))

In A8 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$8:A8)>$A$6,"",SMALL(IF(Rep=$A$1,IF(ISNUMBER(MATCH(Status,$A$3:$A$4,0)),Ivec)),ROWS($A$8:A8)))

In B8 just enter and copy down:

=IF($A8="","",INDEX(Client,$A8))

In C8 just enter and copy down:

=IF($A8="","",INDEX(Status,$A8))

In D8 just enter and copy down:

=IF($A8="","",INDEX(Amount,$A8))

In E8 just enter and copy down:

=IF($A8="","",INDEX(Quantity,$A8))

 

[CreativeRova]

Hi Aladin,

a few questions. Are you assigning an index number to each rep? if so why? you are using a few formula I don't know so am having some issues getting it to work in the actual spreadsheet.

Also the kist needs to be sorted by 2 factors, rep and status. Only show Karen Confirmed. on another page it will show Karen Pending and another for Karen Lost.

 

[Aladin Akyurek]

Hi Aladin,

a few questions. Are you assigning an index number to each rep? if so why?

Detemining at which rows are the relevant records are located. This, for reasons of efficiency.

you are using a few formula I don't know so am having some issues getting it to work in the actual spreadsheet.

If you follow the instructions for naming and the formulas as is the implementation/adaptation won't be that difficult.

Also the kist needs to be sorted by 2 factors, rep and status. Only show Karen Confirmed. on another page it will show Karen Pending and another for Karen Lost.

karen lost (this sheet processes the data for rep = Karen and status = lost)

Row\Col	A​	B​	C​	D​
1​	karen
2​
3​	lost
4​
5​
6​	1
7​	Idx	Client	Amount	Quantity
8​	5	Client 6	600	4
9​

A6, just enter:

=COUNTIFS(Rep,A1,Status,A3)

A8, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$8:A8)>$A$6,"",SMALL(IF(Rep=$A$1,IF(Status=$A$3,Ivec)),ROWS($A$8:A8)))

B8, just enter and copy down:

=IF($A8="","",INDEX(Client,$A8))

C8, just enter and copy down:

=IF($A8="","",INDEX(Amount,$A8))

D8, just enter and copy down:

=IF($A8="","",INDEX(Quantity,$A8))

 

[CreativeRova]

Hi Aladin,

Sorry, I can not get this to work at all. I have even made a new spreadsheet to look exactly like what you have above and entered the formulas you have written by copy and pasting so they are exactly the same, changed the "rep" to be Karen changed the "status" to be Lost and everywhere there is a formula I get errors #NAME?

Also I cant see where your formula is looking up the data from master spreadsheet.

 

[CreativeRova]

Is anyone else able to explain what I am doing wrong with Aladins solution or have another option?

 

[Aladin Akyurek]

Hi Aladin,

Sorry, I can not get this to work at all. I have even made a new spreadsheet to look exactly like what you have above and entered the formulas you have written by copy and pasting so they are exactly the same, changed the "rep" to be Karen changed the "status" to be Lost and everywhere there is a formula I get errors #NAME?

Also I cant see where your formula is looking up the data from master spreadsheet.

Either you don't have (1) COUNTIFS on your system or (2) the definitions you are asked to implement did not go well.

Here is the workbook which implements the set up: Dropbox - CreativeRova sublist.xlsx

If you don't have COUNTIFS, replace the formula in A6...

=COUNTIFS(Rep,A1,Status,A3)

with the following:

=SUMPRODUCT(--(Rep=A1),--(Status=A3))

 

[CreativeRova]

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.

The master version has many columns that I haven't listed as I though I would be able to translate the answer across any number. Since I am having issue here is the full layout of the master workbook.

July

July

July

August

August

August

Client

Agency

BDM

Value

Format

NEW

Status

Media

Production

Quantity

Media

Production

Quantity

SUM

SUM

SUM

SUM

SUM

SUM

SUM

Client 1

Blah

Karen

$10,000

Ad

YES

PENDING

$4,000

$2,000

50

$4,000

$0.00

50

Client 2

Blah

Kim

$15,000

Ad

NO

Confirmed

$12,000

$2,000

100

$1,000

$0.00

15

<tbody>
</tbody>

The above is updated constantly every day.

The sales persons version only shows their deals and sums. There will be 13 tabs on the bottom a summary tab and 12 months. The below is an example of JULY

Karen July

Budget

$50,000

Confirmed

E9

Difference

% of Budget

Lost

L9

Pending

S9

Confirmed

Sum E11:E:2000

Lost

Sum L11:L2000

Pending

Sum S11:S2000

Client

Agency

Value

Format

Monthly Value

Production

Client

Agency

Value

Format

Monthly Value

Production

Client

Agency

Value

Format

Monthly Value

Production

<tbody>
</tbody>

So I need A11 to pull the info from the master workbook. but only the selected info not every line. It also needs to only show in the July tab if there is an amount in July if the Media amount is in August Media then show in the August tab.

This question I think is a lesson to me in be a lot more specific and clear when asking a question to the forum.

Thanks.