Not sure how to move forward

[jdub21]

Hello,
I am reaching out to anyone for guidance on this one. Here is the detailed scenario

• I have a Sales Commission Report in excel. There are 5 worksheets plus summary sheet
• Each Month we add the new onboarded client under reps name for 90 days
• Looking to have Summary sheet automatically update with the new client under Sales Reps name
• Vice versa, when we remove the client, it removes from Sales Reps name on the summary sheet.
• To go one step further, when the client is added, I want to sum all sales by month based on the "Client Name" as the identifier.

Ultimately I am looking to automate the Sales Commission Report completely so no one can screw with the "Numbers"

 

[SanjayGMusafir]

Summary sheet automatically update with the new client under Sales Reps name

Which Exact version of Excel are you working with? You have put all from 2019 to 365 in your profile.

The solution will depend on the version you are using...

 

[jdub21]

I am using 365. I will update my profile.

 

[SanjayGMusafir]

I am using 365. I will update my profile.

With 365 you have many advantages.

The one you need right now is called SPILL Range which automatically populates change in the base data.

What you can do is - Populate Sales Rep names using UNIQUE function. This way it will automatically change as we change the base data.

For any further help, Please post sample data as well as sample of summary sheet using XL2BB. Hope some solution could be delivered.