Creating a workbook to calculate and track monthly commissions

SM mechanic

New Member
Joined
Sep 12, 2017
Messages
20
So I run a dealership with 8 service advisors, their pay depends on multiple different things, related to their sales, customer satisfaction scores etc etc. I currently use a workbook I created with individual sheets for each advisor, which is basically a calculations page I input their various sales numbers, goals etc. That I also use to print out to go over each advisor's performance each month.

Then there is one master sheet that has all of them together, with the numbers from the calculations sheets for each advisor for each month, and it's a rolling thing. So every month can be seen as the year goes along, and that's what the payroll department uses to cut their checks

Currently I manually move the numbers from the calculations sheets to the master sheet, because each month I clear out the calculations sheets and put in the new numbers for that month. I was trying to get excel to move those numbers on its own. I wrote a formula I thought would work, that filled in the cells on the master sheet based on the month that was typed into a cell on the calculations sheets. The problem I found is, it doesn't save the info on the master month to month when I tested it.

IE on the calculations sheets I put in JAN in the correct cell and run all the numbers, on the master the column under that advisors name fills in the numbers for January. However when I change the month to FEB on the calculations sheet, January's numbers go away, and February's number populate.

I hope I explained this correctly.

Is there some way I can make it keep the numbers in the master sheet month over month? Or do I need to redesign the entire work book?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

SM mechanic

New Member
Joined
Sep 12, 2017
Messages
20
I am still messing with it on my own, I just had the idea to put the cell in the calculation sheet that I referenced in my formula on the master sheet into a drop down with all the months there. I got that to work, but it still doesn't save the data month to month. It just moves the numbers on the master sheet to whichever month the drop down is set to.

Not sure if I am in the vicinity of a solution or if I am totally going off in the wrong direction.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,212
Messages
5,623,424
Members
415,974
Latest member
ZorroOP

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
Top