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]

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

D​

[/td][/tr][tr][td]

2​

[/td][td] $1,500 [/td][/tr]
[tr][td]

3​

[/td][td] $500 [/td][/tr]
[tr][td]

4​

[/td][td] $2,500 [/td][/tr]
[tr][td]

5​

[/td][td] $1,420 [/td][/tr]
[tr][td]

6​

[/td][td] $600 [/td][/tr]
[tr][td]

7​

[/td][td] $1,900 [/td][/tr]
[tr][td]

8​

[/td][td] $6,300 [/td][/tr]
[tr][td]

9​

[/td][td] $800 [/td][/tr]
[tr][td]

10​

[/td][td] $750 [/td][/tr]
[/table]

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)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] karen[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

2​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

3​

[/td][td] lost[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

4​

[/td][td] pending[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

6​

[/td][td] 2[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

7​

[/td][td] Idx[/td][td] Client[/td][td] Status[/td][td] Amount[/td][td] Quantity[/td][/tr]
[tr][td]

8​

[/td][td] 1[/td][td] Client 2[/td][td] Pending[/td][td] 1500[/td][td] 9[/td][/tr]
[tr][td]

9​

[/td][td] 5[/td][td] Client 6[/td][td] Lost[/td][td] 600[/td][td] 4[/td][/tr]
[tr][td]

10​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

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)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td] karen[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

2​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

3​

[/td][td] lost[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

6​

[/td][td] 1[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

7​

[/td][td] Idx[/td][td] Client[/td][td] Amount[/td][td] Quantity[/td][/tr]
[tr][td]

8​

[/td][td] 5[/td][td] Client 6[/td][td] 600[/td][td] 4[/td][/tr]
[tr][td]

9​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]July
[/TD]
[TD]July
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[TD]August
[/TD]
[TD]August
[/TD]
[/TR]
[TR]
[TD]Client
[/TD]
[TD]Agency
[/TD]
[TD]BDM
[/TD]
[TD]Value
[/TD]
[TD]Format
[/TD]
[TD]NEW
[/TD]
[TD]Status
[/TD]
[TD]Media
[/TD]
[TD]Production
[/TD]
[TD]Quantity
[/TD]
[TD]Media
[/TD]
[TD]Production
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SUM
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SUM
[/TD]
[TD]SUM
[/TD]
[TD]SUM
[/TD]
[TD]SUM
[/TD]
[TD]SUM
[/TD]
[TD]SUM
[/TD]
[/TR]
[TR]
[TD]Client 1
[/TD]
[TD]Blah
[/TD]
[TD]Karen
[/TD]
[TD]$10,000
[/TD]
[TD]Ad
[/TD]
[TD]YES
[/TD]
[TD]PENDING
[/TD]
[TD]$4,000
[/TD]
[TD]$2,000
[/TD]
[TD]50
[/TD]
[TD]$4,000
[/TD]
[TD]$0.00
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]Client 2
[/TD]
[TD]Blah
[/TD]
[TD]Kim
[/TD]
[TD]$15,000
[/TD]
[TD]Ad
[/TD]
[TD]NO
[/TD]
[TD]Confirmed
[/TD]
[TD]$12,000
[/TD]
[TD]$2,000
[/TD]
[TD]100
[/TD]
[TD]$1,000
[/TD]
[TD]$0.00
[/TD]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Karen July
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Budget
[/TD]
[TD]$50,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Confirmed
[/TD]
[TD]E9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]% of Budget
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lost
[/TD]
[TD]L9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pending
[/TD]
[TD]S9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Confirmed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum E11:E:2000
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lost
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum L11:L2000
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pending
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum S11:S2000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client
[/TD]
[TD]Agency
[/TD]
[TD]Value
[/TD]
[TD]Format
[/TD]
[TD]Monthly Value
[/TD]
[TD]Production
[/TD]
[TD][/TD]
[TD]Client
[/TD]
[TD]Agency
[/TD]
[TD]Value
[/TD]
[TD]Format
[/TD]
[TD]Monthly Value
[/TD]
[TD]Production
[/TD]
[TD][/TD]
[TD]Client
[/TD]
[TD]Agency
[/TD]
[TD]Value
[/TD]
[TD]Format
[/TD]
[TD]Monthly Value
[/TD]
[TD]Production
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.