Looking for help on a formula for a sales team

[segram77]

I manage a group of in-home sales reps. I have a spreadsheet of all of the appointments they have been on. It is organized with one rep for each spreadsheet tab. Each tab is organized to show how the reps closing rate for different products in different cities. I would like to make a main sheet that would have all the cities on it with a formula that would look at each reps sheet to find which rep closes the highest in each given city for each product and then display that reps name on the main sheet for that product. Not sure if Vlookup or something the like would let me do something like that or if its even possible. Can anyone give me a hand Please?

Here is a better description of what i need. The "Data" tab is where i will be entering the mass data. All other sheets have formulas to pull from that sheet. So here is the break down. The two tabs with names on them pull data for the individual reps name for closing ratios( Columns J, N, R, etc) in total and for each product(row 1) for each respective city( Column A). What I need help with is the sheet labeled "tracking" I would like a formula that will look at each sheet I create with a reps name. Then on the "Tracking" sheet next to each city under the respective product (Column c, D, E, F, G, etc) it will look at all sheets with names and find the highest closer for that city and product and pull there name to that cell. For example on the "Tracking" Sheet right now under column C row 4 you can see that Murray Fanning (Manually entered as of now) is the highest closer for roofing in the city of Westfield. However if two sheets with rep names on them have the same closing rate then the name of the person who ran the most appointments for the product (example sheet (joshua Meskill column G for roofing product) would appear in the cell on the "Tracking" sheet

[José Augusto]

Hi
Welcome to the forum.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[segram77]

Hi
Welcome to the forum.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I have attached a copy of the sheet "desensitized" The only answer i dont have is what to do if two people have sold the same amount. I am open to suggestion. Probably the person with the most amount of appointments in that city.

[Phlegon_of_Tralles]

Can you provide some (fake) data so that we can make an analysis? I'm not quite sure what you are wanting us to do. Also, please don't attach a csv, I think those generally cut off the other tabs (if there are any).

[segram77]

i have edited the description for a more detailed explanation and uploaded the excel file

[José Augusto]

Hi @segram77

No files added.

[segram77]

sorry added now not sure why i didnt attach the first time

[José Augusto]

Hi

Have you tried a pivot table?

I suppose you must invest in a pivot table, and to make a few changes in data sheet to avoid having more than one Appointment Result / Type such as Bathroom & Windows in the same cell (row).

In the file you have a new sheet 'Report' with a sample pivot table. See if that helps you.