Lookup function for sales tracking workbook

[pghhdh]

Hi friends! Looking for some help here with formulas (trying to do this without VBA); I've done something similar years ago and can't remember how I did it! I am working on a sales tracking workbook. I have Zapier dumping raw data into the workbook from my transaction database and am filtering the data from there. I have a list of sales reps and their respective transaction on the data dump page. I would like to have each respective rep's sales automatically generated into their own page within the workbook.

Column A of the data dump page contains the reps' names, and Columns C thru Q contain the sales details for each transaction. I need some sort of lookup function on each rep's page in the workbook that pulls only their sales.

For instance, this is what the beginning of a row in the data dump page looks like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Rep[/TD]
[TD]Client[/TD]
[TD]Contact[/TD]
[TD]Time[/TD]
[TD]Sale[/TD]
[TD]Units[/TD]
[TD]Recurring[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]ABC Inc.[/TD]
[TD]Mike Jones[/TD]
[TD]10am[/TD]
[TD]$2mm[/TD]
[TD]16,500[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated, please and thanks!

 

[Fluff]

Hi & welcome to MrExcel.
With data like

Book1
	A	B	C	D	E	F	G
1	Rep	Client	Contact	Time	Sale	Units	Recurring
2	Sally	ABC Inc.	Mike Jones	10am	$2mm	16,500	No
Sheet1

Book1
	A	B	C	D	E	F
1	Client	Contact	Time	Sale	Units	Recurring
2	ABC Inc.	Mike Jones	10am	$2mm	16500	No
Sheet2

Cell Formulas
Range	Formula
A2	=INDEX(Sheet1!B$2:B$17,AGGREGATE(15,6,(ROW(Sheet1!B$2:B$17)-ROW(Sheet1!B$2)+1)/(Sheet1!$A$2:$A$17="Sally"),ROWS($A$1:$A1)))

dragged across & down

 

[pghhdh]

Thank you, Fluff! Works perfectly!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Toadstool]

Fluff beat me! I must type faster....



Hello pghhdh,

My first comment is that "automatically generated into their own page within the workbook." seems to suggest a tab for each salesperson. Generally I avoid multiple tabs where each is a data attribute (i.e. avoid tabs for Jan, Feb, Mar, etc. or Region1, Region2, Region3, etc) as formulae across worksheets can get tricky.

I'll assume you have a list of salesperson names (although when everybody has the =UNIQUE function available it will make this type of thing simpler) so put them on a sheet in a table "SalesReps" and give that column a name.

Now you can create your "Reporting" sheet which begins with an LoV to select a name from the Salesreps. It can then do INDEX/SMALL or AGGREGATE and COUNTIFS, SUMIFS, etc. against your data dump sheet to retrieve and summarize the data. You can also enter "*" into the LoV selection to retrieve all SalesReps to get grand totals.

Does that sound like the kind of solution you're after?

Regards,
Toadstool