Auto Populate one table from another

[PaulPlumridge]

Good Morning
I hope you are all well. I'm looking for some help on on designing commission sheets for my sales team.
We have a master file for 2019 that lists every commission our company earns in roughly this format

Date / Month / Salesman / Invoice No / Customer / Comm £

Each day new deals get added as new lines on the bottom of the master data.

The sales guys have their own sheets for each month with the same headers as above - what i am looking is a way for their individual sheets to automatically update the data from the master file.
Is this even possible? if so what would the formula structure look like

Thank you for your help

 

[Legacy 436357]

Hello Paul,

I hope this helps you:

Excel Workbook
	A	B	C	D	E
1	Joe
2
3	Date	Month	Invoice No	Customer	Comm E
4	1/8/2019	Jan	369	Acme	$ 125.00
Sheet2

 

[PaulPlumridge]

Thank you for your help.
I understand the theory here and think there is scope for this to work. What i am struggling with is i have a potentially 2000 line document for the master file but for each month an individual salesmans sheet with only 20 lines in.
So i want "John Smiths" sheet to populate with any time "John Smith" appears in the master file

What i need is the individuals sheet to return any line of theirs that appears in the master
Also it isnt seperate sheets on one file but in different files. does this complicate

 

[excel_learnerz]

the below will work if for example you pull sales number from master sheet into sales agent sheet for arguments sake the sales agent name was in column e,

{=IFERROR(INDEX(MasterSheet!$A$2:$A$2000,SMALL(IF(MasterSheet!$E$2:$E$2000="John Smith",ROW(Workings!$A$2:$A$2000)-MIN(ROW(Workings!$A$2:$A$2000))+1),ROWS($A$2:A2))),"")}

you have to hit shift + ctrl and enter for the {} brackets to appear, when you drag this formula down you have hold Ctrl, put that in each cell in sheet 2 referencing which cell you want to add from master sheet as in the above if column A is date then it would pull that value in,

 

[PaulPlumridge]

Hi Excel Learner
I'm nearly there i appreciate your help so much.

This is the layout of my master file with which column is which. Below is the commission sheet and the blue text is populated by a drop down box and examples below.

How do i include in your formula to populate by account manager and by month





[TABLE="width: 1380"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date Paid Out[/TD]
[TD]Account Manager[/TD]
[TD]Team[/TD]
[TD]Lender [/TD]
[TD]Invoice Number[/TD]
[TD]Customer [/TD]
[TD]Month[/TD]
[TD]Type[/TD]
[TD] Commission
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]Jan[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 2309"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 14"][/TD]
[/TR]
[TR]
[TD]Account Manager[/TD]
[TD]Team[/TD]
[TD]Month[/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="colspan: 2"]John Smith Blue Jan[/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]Account Manager[/TD]
[TD]Team[/TD]
[TD]Lender [/TD]
[TD]Invoice Number[/TD]
[TD]Customer [/TD]
[TD]Commission[/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]

 

[PaulPlumridge]

Hi again

I've almost got it working ignoring the month bit.
Using This formula

=IFERROR(INDEX('[2019 Sales Report.xlsx]Raw Data'!$F1:$F200,SMALL(IF('[2019 Sales Report.xlsx]Raw Data'!$B1:$B200=A5,ROW($C$5:$C$31)-MIN(ROW($C$5:$C$31))+1),ROWS($C$5:C5))),"")

2019 sales report = my master commission file
column F is the customer
Column B is the account manager

the range in my commission sheet i want the array to sit is rows 5-31

Now it isnt returning based on account manager but only whats in row 5 of my master data and the array wont drop down into any other lines?

 

[excel_learnerz]

Hey, you have the value set at A5 so if you want to lock it to that cell make it look like $A$5 so its an absolute reference and not a relative one, (using the persons name might be easier for that also as I did in the original ="John Smith")

I am a little confused, in A you have date paid so on master sheet example so is that the name of the person or date?

can you tell me on commission sheet what the columns are, and on master sheet what the columns are as it looks confusing from your post,

on the commission sheet which columns from the master sheet are you trying to pull im eg Column A is account manager, column B is date etc,

 

[PaulPlumridge]

Hi Mate - thanks for replying

The columns are mostly the same but not in exactly the same columns

The top of the commission sheet is a drop down for each salesman. i want their sheets to populate for each line they appear in the master data file

 

[PaulPlumridge]

darnit i thought that would insert a picture

A B C D E F G H I
[TABLE="width: 1154"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date Paid Out[/TD]
[TD]Account Manager[/TD]
[TD]Team[/TD]
[TD]Lender [/TD]
[TD]Invoice Number[/TD]
[TD]Customer [/TD]
[TD]Month[/TD]
[TD]Type[/TD]
[TD] Commission B4 Introducer Fees [/TD]
[/TR]
[TR]
[TD]02/01/2019[/TD]
[TD]John Smith[/TD]
[TD]Telesales[/TD]
[TD]Bank 1[/TD]
[TD]101[/TD]
[TD]Customer 1[/TD]
[TD]Jan[/TD]
[TD]Lease[/TD]
[TD]£1,000.00[/TD]
[/TR]
[TR]
[TD]02/01/2019[/TD]
[TD]Jane Doe[/TD]
[TD]Junior[/TD]
[TD]Lender 2[/TD]
[TD]102[/TD]
[TD]Customer 2[/TD]
[TD]Jan[/TD]
[TD]Loan[/TD]
[TD]£2,000.00[/TD]
[/TR]
[TR]
[TD]02/01/2019[/TD]
[TD]Alan Jones[/TD]
[TD]Senior[/TD]
[TD]Broker 3[/TD]
[TD]103[/TD]
[TD]Customer 3[/TD]
[TD]Jan[/TD]
[TD]HP[/TD]
[TD]£5,000.00[/TD]
[/TR]
</tbody>[/TABLE]


A B C D E Other columns are required
[TABLE="width: 718"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Account Manager[/TD]
[TD]Team[/TD]
[TD]Lender [/TD]
[TD]Invoice Number[/TD]
[TD]Customer [/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Telesales[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Telesales[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Telesales[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[excel_learnerz]

hey, so ok if the top of the sheet is a drop down list of account manager and by selecting this value then the commission fields should populate,

so based on the assumption that eg Cell A1 is a drop down list and assuming the headers above (account manager, team, lender, invoice number etc) would be entered into A2, B2, C2 D2 etc.

so A3 = $A$1 (the drop down cell with absolute reference, and dragged down so all equal the account manager selected) ha I know its not set up like that probably just so we are on the same page regarding set up.

Your master sheet has a lot of data that's continuously added to so set a large range in the formula, if you want the team for account manager in column B, ("Team" is in B2 so data starts in B3)

{=IFERROR(INDEX('[2019 Sales Report.xlsx]Raw Data'!$C$2:$C$2000,SMALL(IF('[2019 Sales Report.xlsx]Raw Data'!$B$2:$B$2000=$A$1,ROW('[2019 Sales Report.xlsx]Raw Data'!$C$2:$C$2000)-MIN(ROW('[2019 Sales Report.xlsx]Raw Data'!$C$2:$C$2000))+1),ROWS($B$3:B3))),"")}​

​

<strike></strike>
when you have that formula entered you hold ctrl + shift then press enter to get the { } brackets to appear (you will need them to work) and once the formula is entered and you want to drag it down press ctrl and hold as you drag down,

ok so the above formula is checking column C in the master sheet (team) and checking against column B in the master sheet (account manager) if values = $A$1, that is the reference at the top of the commission sheet I mentioned earlier where a drop down list is and where you select which agent you pick checked in column C (team) for each additional value where the chosen account manager corresponds to the value we want, then at the end in column B in commission sheet (team) as this formula is entered into cell B3

this formula worked across the different headers you want so for example in column C in commission (lender) you would paste in C3 which would pull in lender

{=IFERROR(INDEX('[2019 Sales Report.xlsx]Raw Data'!$D$2:$D$2000,SMALL(IF('[2019 Sales Report.xlsx]Raw Data'!$B$2:$B$2000=$A$1,ROW('[2019 Sales Report.xlsx]Raw Data'!$D$2:$D$2000)-MIN(ROW('[2019 Sales Report.xlsx]Raw Data'!$D$2:$D$2000))+1),ROWS($C$3:C3))),"")}​

​

​

<strike></strike>


I hope that helps a little and makes some sense