I have a spreadsheet with 5 tabs in the same format, maintained by different groups. Each tab has rows of task names and an assigned person, among other fields. We need a summary tab. We use Microsoft Teams, so I can't use pivot tables, as they are incompatible with shared edit/downloads. But filters on the summary work fine.
Quick and dirty solution - Manually make summary by typing in the 2 cell references for the first row of each sheet, paste down for enough rows, change cells to absolute references. Add filters. This was a quick fix, but it doesn't cope well when the teams add lines. I need to manually update again.
Sheet 1
Task1, Person1
Task2, Person2
Sheet 3
Task3, Person2
Task4, Person3
Summary Sheet
Task1, Person1
Task2, Person2
Task3, Person2
Task4, Person3
1. Is there a better way to copy the data to summary so it stays updated as people add/remove/update lines in their own tabs
2. Is there a quick way to convert a range of cells to an absolute reference other that using replace function to add $?
Quick and dirty solution - Manually make summary by typing in the 2 cell references for the first row of each sheet, paste down for enough rows, change cells to absolute references. Add filters. This was a quick fix, but it doesn't cope well when the teams add lines. I need to manually update again.
Sheet 1
Task1, Person1
Task2, Person2
Sheet 3
Task3, Person2
Task4, Person3
Summary Sheet
Task1, Person1
Task2, Person2
Task3, Person2
Task4, Person3
1. Is there a better way to copy the data to summary so it stays updated as people add/remove/update lines in their own tabs
2. Is there a quick way to convert a range of cells to an absolute reference other that using replace function to add $?