Creating a workbook to calculate and track monthly commissions

[SM mechanic]

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?

 

[SM mechanic]

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.