Complex excel formula help needed

[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 for each city this would change as information sales data changed. 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?

[#2]

@Segram77Since the rep name is not included in the data and only mentioned in the sheet or tab name, I think you will need to write some VBA to get into this. Although I think it would be much easier to get it if all the data with rep names are included in only one sheet, where each row represents a visit, with all needed attributes.

Then we can use some formulas or pivot tables (or both) to calculate the rates and generate the rport.

Please tell me if you need further help.