How do I create an assignments summary sheet?

EnochSmith

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?

Andrée Starå

Hi @EnochSmith

I hope you're well and safe!

What about using the conversation feature?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi @EnochSmith

I agree with @Andrée Starå - it sounds like Reports really are the best way to proceed here, if you need to filter by Column and potentially Group by Facility, then you can use the Conversations feature to take notes for each row, or multiple notes on the same row.

You could embed the Report on a dashboard if you also needed other elements to be available at the same time (ex. a Form open so you could enter new rows into a specific sheet while looking at the Report).

Or, if your plan has access to it, you may want to look into WorkApps where you can house multiple Reports in one place.

Cheers,

Genevieve

Andrée Starå

Hi @EnochSmith

I hope you're well and safe!

What about using the conversation feature?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi @EnochSmith

I agree with @Andrée Starå - it sounds like Reports really are the best way to proceed here, if you need to filter by Column and potentially Group by Facility, then you can use the Conversations feature to take notes for each row, or multiple notes on the same row.

You could embed the Report on a dashboard if you also needed other elements to be available at the same time (ex. a Form open so you could enter new rows into a specific sheet while looking at the Report).

Or, if your plan has access to it, you may want to look into WorkApps where you can house multiple Reports in one place.

Cheers,

Genevieve

EnochSmith

Thanks, I think you're both right - the Conversations feature in tandem with a filter or a report is probably the best option. Thank you both!

Andrée Starå

@EnochSmith

Excellent!

Happy to help!