How to update all Cell References Each month automatically rather than Manually

[UptownJogger]

Good morning,

I have Excel 2010 and I should have asked this question years ago, but better late than never I suppose. I am just hoping there is a more efficient way to do what I have been doing for years.

I report every month some of my staffs activity from the previous month.
I have on the excel file a sheet that lists all my employees. For Each employee, I have a column for each of their activity. Then I have a 12 rows, one row for January, one for February ect. Down the rows, I would list another employee, and list the January - December rowns for them (all have the same activities so they all have the same columns.

I on the same file have a excel sheet that shows the activity for the month that just passed an the respective employees activity for that month (and next to it a year to date column). I would cell reference the sheet I described above and since this sheet shows a specific month. I would reference the line items in the other sheet for that specific month. The next month, I would then one by one for each cell have to change the cell reference for each employee by typing in the next row number. Since I have many employees and many activities, going row by row is got tedious.

So for example, I would enter on one sheet Smiths # of closings for each month for the year. So at the end of the month I would enter in a new line item. On another sheet on the File, I may reference say Smith's January sales as that sheetname B1. And I would reference for that some month Jone's sales for January as sheetname B21. In February I would then manually go into the presentation sheet and change Smith's cell reference to B2 and Jones to B22. For March activity, change Smith's to B3 and Jones to B23, etc. Instead of going one by one for each cell everymonth when I have to do a new report, is there an efficient way that I can have the cells that are referring to the sheet with the data to go to the next row which references the next month? So in essence, Is there a way I can do a change all for cell references that would make all reference cells change so it reports the activity for the new month that I have listed on another sheet?

Thanks
Uptown Jogger

 

[RasGhul]

It would be easier to choose a staff member and month or month(s) as criteria then a formula would calculate activity.

Can you post some sample data so we can look at it for you? Try to include what you expect the result to look like.

 

[UptownJogger]

It would be easier to choose a staff member and month or month(s) as criteria then a formula would calculate activity.

Can you post some sample data so we can look at it for you? Try to include what you expect the result to look like.

Yes. I have a file that has multiple worksheets. I have a sheet Toward the front titled "Monthly". My manager looks at this sheet in the file every month. I am about to send him this file and on the sheet called "Monthly" I am going to update it so the top reads "January 2019". He will see my each of my staff member's productivity for the Month of January, what was expected of them in January, the deviation from Projected and Actual results for January, and some Year to date columns.

Many cells on this sheet are tied to a worksheet in the file called 2019Data. On this 2019Dat worksheet, I list out all my employees. And underneath their name going down the sheet I have a line item for January, for February, March, etc.
I then down the sheet have the next employee and the months of the year. I have their tasks on a colums. The first fews columns has the actual outputs for each specific month in 2019 (output varies from month to month), and I have a columns further on the right for expectedl results. Obviously, I only have the actual results for each employee for just the month of January now. Employees all have the same tasks, but there are multiple tasks, so therefore many columns (each having a expected out put then next to that task an actual output).

So when my manager looks at the worksheet Monthly.
When he looks at the the cell for the number of accounts Mr. Smith was supposed to create for January, it is referring to the 2019 Data worksheet and reads
='2019Data'!$M6
Likewise, the column that shows Mr. Smiths actual results in January 2017 refers to the same 2019 data worksheet (note, I have expected results in columns further to the right in the 2019Data worksheet)
='2019Data'!$D6

Next Mr. Smith's outpout on the Monthly page some colums down, I have Mr. Jones' output. His Information is about 15 rows down from Smith on the 2019 Data sheet since I had to list the months of the year for each employee . So the number of accounts he is actually did in January reads
='2019Data'!D21

As months pass, I will keep on entering numbers on the 2019 data Worksheet. I will enter next month all the different units of labor for February.
When I update the "Monthly" worksheet for my manager to read an present for February results, unless there is a better way, I will have to manually change every cell that has a reference to 2019Data Worksheet to refer all the line items pertaining to February.
So I would have to go into Mr. Smiths February expectation for the # of actual accounts created and change the "6" to a "7" so the cell reference for February's output would be
='2019Data'!$D7
and Mr. Jones' cell reference for # of accounts for February, I would have the change "21" to "22" for it to be
='2019Data'!D22

Given I have many staff and they have many tasks, you could see how upping every cell reference by 1 ( the next month items are in the line below in the 2019Data worksheet) can be quite tedious. Is there a better way that once I enter in on 2019data February's numbers, I can have all cell references in Monthly then refer to February's results in the 2019Worksheet (so in other words, increase the cell reference by 1 row).

thanks
dan