| A boss has 13 Employees with a separate excel spreadsheet for each one. |
| Each employees' spreadsheet contains 12 tabs; one for each month with a report he/she completes based on performance factors. |
| In each employee's report, columns D9-11 & D15-17 contain institutions and E9-11 & E15-17 contain # of hearings completed at each of those corresponding institutions. The same is true for G9-11 & G15-17 (contains institutions) & H9-11 & H15-17 (contains # of hearings completed). |
| As each of the employees fill in their data from each of their monthly reports, I have them linked to auto-populate a cumulative spreadsheet for their boss to analyze yearly totals as each month progresses. |
| In the cumulative spreadsheet, I did this by summing the number of hearings completed each month to the previous cumulative total, but I'm not sure how to handle a situation where an employee acquires new institutions or switches institutions during the year. I think they just need to leave their institution names alone once entered, and use a new row, if they acquire a new one, but if they accidentally change Institution 1 to Institution 5 in May or later in the year, right now that change won't show up on the cumulative spreadsheet because the institution names are only linked (pulling from) the April Monthly Reports . I can't lock the cells because the employees may have three institutions one month, pick up a fourth one the next month and then exchange two of those institutions three months later for two different ones. He or she could also then pick up one of the exchanged institutions again later in the year. How can I update the institution location names (which can change periodically) on a cumulative spreadsheet and make sure they accurately reflect the correct total of corresponding hearings completed in those locations? Additionally, in the boss's cumulative spreadsheet, columns D, F, H, J, L, N contain the institution names, and columns E, G, I, K, M, O contain the cumulative totals of the number their hearings at the preceding column's institution. The Institution name columns (D-N) on the cumulative spreadsheet are all only auto-populating the locations from columns D & G in the employees' monthly report for April (the start of their organization's year.) How do I make the columns (D-N) on the cumulative spreadsheet update for the month of May, June, etc.? That way, if he prints/pdf's each month's report, at least the boss can look for the change and "fix" it, in case one of the employees does actually change a location accidentally. But wait, there's more!  Let's say it's August 15th; employees have been filling in their data monthly, but my husband didn't have time to run the reports on the 5th of each month for July and August. How can I give the boss the ability to change a date somewhere (cell a17 is empty on the cumulative spreadsheet) to tell Excel, "Give me the cumulative totals as of 7/5/23" or 8/5/23, or any date really?I feel like I should also mention the data in the cumulative sheet is going to be linked to to auto-populate a form twice a year for a mid year and annual evaluation. The other use for the data is simply to run tables or charts to show higher administration levels progress/deficiencies requiring more staff in certain areas. |
| I'm definitely in over my head and really could use some help. I feel like a phone conversation would be helpful for this, but I don't know if that's even allowed here. Is this something I need to hire someone to help me with or is it something I can solve on my own with help here? |
Attachments
Last edited:
