Hi! My problem is this:
- a manager is emailing out spreadsheets weekly to her team for them to fill in their current workload. Design includes Name at B4, and completed information about workload in cells A11 to Hx (x depends, more/less rows are filled in each week)
- team email back the spreadsheets each week. Manager is currently copying and pasting to form one overview of all team members (there are about 5 of them)
- I'd like to have a 6 page workbook, so that the team member responses can be pasted into tabs 2-6 each week, and tab 1 displays a consolidated view. It should have an additional column which references the name in cell B4 for each team member.
- for example, if team member A is Peter, then Peter's returned spreadsheet will be pasted into tab 2. Team member B, Debbie, will be pasted into tab 3, and so on. Peter has filled in 10 rows, and Debbie has filled in 8. I'd like tab 1 to have headers matching the other tabs, but moved along on column to the right, and column A becomes 'Name'. So A2-A11 should pull through 'Peter' from B4 on tab 2. B2-B11 will reference A11-A20 on tab 2, and so on.
- the key is that once the lines become blank on tab 2 (so row 21 onwards, as Peter filled in 10 rows) then I need a jump to reference tab 3 to show Debbie's data directly underneath, with no blank lines showing up on tab 1.
Are there formulas that will do this? It needs to be flexible to reflect the changes in row numbers and data each week.
(note: already suggested to manager that she has one workbook, able to be edited by more than one user, where everyone updates info in the same sheet, but has rejected that as worried about honesty if available to be viewed by all)
- a manager is emailing out spreadsheets weekly to her team for them to fill in their current workload. Design includes Name at B4, and completed information about workload in cells A11 to Hx (x depends, more/less rows are filled in each week)
- team email back the spreadsheets each week. Manager is currently copying and pasting to form one overview of all team members (there are about 5 of them)
- I'd like to have a 6 page workbook, so that the team member responses can be pasted into tabs 2-6 each week, and tab 1 displays a consolidated view. It should have an additional column which references the name in cell B4 for each team member.
- for example, if team member A is Peter, then Peter's returned spreadsheet will be pasted into tab 2. Team member B, Debbie, will be pasted into tab 3, and so on. Peter has filled in 10 rows, and Debbie has filled in 8. I'd like tab 1 to have headers matching the other tabs, but moved along on column to the right, and column A becomes 'Name'. So A2-A11 should pull through 'Peter' from B4 on tab 2. B2-B11 will reference A11-A20 on tab 2, and so on.
- the key is that once the lines become blank on tab 2 (so row 21 onwards, as Peter filled in 10 rows) then I need a jump to reference tab 3 to show Debbie's data directly underneath, with no blank lines showing up on tab 1.
Are there formulas that will do this? It needs to be flexible to reflect the changes in row numbers and data each week.
(note: already suggested to manager that she has one workbook, able to be edited by more than one user, where everyone updates info in the same sheet, but has rejected that as worried about honesty if available to be viewed by all)
