Lookup function for sales tracking workbook

pghhdh

New Member
Joined
Aug 17, 2019
Messages
6
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:
RepClientContactTimeSaleUnitsRecurring
SallyABC Inc.Mike Jones10am$2mm16,500No

<tbody>
</tbody>

Any help would be appreciated, please and thanks!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi & welcome to MrExcel.
With data like

Book1
ABCDEFG
1RepClientContactTimeSaleUnitsRecurring
2SallyABC Inc.Mike Jones10am$2mm16,500No
Sheet1



Book1
ABCDEF
1ClientContactTimeSaleUnitsRecurring
2ABC Inc.Mike Jones10am$2mm16500No
Sheet2
Cell Formulas
RangeFormula
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top