I'm working with a healthcare department, and our staff are assigned cases to contact trace. They contact the individual or facility, enter in the relevant info to a master sheet, take whatever notes they need, and then mark it as completed.
The problem I'm having is creating an assignments summary sheet. Each investigator needs to be able to conveniently look up the last time they contacted the facility, see what specific information needs updating, see their notes for each case, and so on.
A report won't work because there are additional notes they have to take that we won't have space for in the master sheet, and you can't add columns to a report that don't exist in the source sheet.
I tried using VLOOKUP, INDEX(COLLECT), and similar functions, but they only return a single case, where each investigator will probably have dozens.
Cell linking sort of works, but you have to routinely adjust where the cells are linked to, because the investigators need several columns and dozens of rows, the rows are constantly moving as new data are added, and there are strict limits on the number of cell links.
Filtering also doesn't work well, since you can't filter columns, and (again) we need to be able to add additional information that we don't have space to store in the master sheet.
As it is right now, we're just replicating data in Excel files to perform the functions we need, but we thought there must be an easier way to do this. Anyone have any ideas?
