Hi! Hopefully my question is worded well enough for it to make sense. Basically here is what I am trying to do:
- First tab is the master data entry sheet. A1 is an employee name. B1-B12 are months (Jan-Dec), C1-12 are sales numbers for each month. Then A13 is another employee name, B13-24 are months, and C12-24 are dollar amounts for that employee. This keeps repeating for about a dozen employees.
- the next several tabs are individual tabs for each employee. On each of these tabs are various formulas that need the dollar amount for the current month to calculate properly. For example, I want to show a percent-to-goal, and an earned commission amount on this tab for sales in August.
The above is easy enough. I can simply paste the number or reference the cell from the first tab and all these formulas work just find. What I would LIKE to do however, is when I go in next week to enter September numbers, I can easily have all of those other tabs reference this new month's number, without having to go in and change the cell reference or re-paste the new numbers on every tab, every month. in other words, currently a cell on the second tab is referencing cell C8 (august sales #), but next month, I want it to reference C9.
Is there a way to do something like this? Like entering the new month into a single cell, which then will change the reference for ALL of those other formulas on the other tabs? Or maybe a macro or something? Any suggestions at all would be a great help!
P.S. - Just for a little more context, the actual workbook i'm working on is a lot more complicated than the above makes it seem. Each employee tab actually has several numbers that need referenced. I would rather spend a bunch of time right now setting up an easy solution each month (like pushing a button or entering a date or other data into a single cell) than having to spend an hour or two on a monthly basis updating those formulas. Also, I'm not just putting all of these numbers and calculations into a single tab because my boss wants a "snapshot" tab for each employee that he can quickly look at each month without having to dig through the whole data tab
