Need ideas for best type of sheet for keeping track of accreditations

Traci Quinn · 12/29/20

I have been tasked with creating a system to keep track of all awards, recognitions, accreditations and designations for a large hospital system with 14 hospitals and two primary affiliates.

The system was created two years ago when two hospital systems merged; both systems had experienced their own individual mergers over the past five years, so there is still quite a lot of diversity in the way hospitals gather and share such information.

My goal is to create one unified space where a variety of departments and personnel from a dozen facilities can provide current information on hundreds of types of information.

I'm new to sheets (and never used Excel in my previous life), and have never done any sort of analytics or data tracing (I'm an English major and a writer) -- so I need some guidance on how to set this up. I'd like to start well so I don't inadvertently create more confusion.

I will need at least four categories:

Awards

Designations

Accreditations

Recognitions

Each of those categories has at least a dozen types of information. In designations, for example, individual hospitals may receive formal designations as a Baby-Friendly Hospital, a Breast Center of Excellence, a Magnet or Pathway to Excellence Hospital, etc. Also, each designation has its own time frame, and each hospital may be working at a different pace, so such designations would expire at different times.

I want this to be a clearinghouse for anyone who has to write a report, a press release, an internal communication, etc. They should be able to look at this sheet and find the superlative they're looking for, see an official, evergreen description of that superlative, see which hospitals are where in their process for this award or designation, and know when those designations etc. expire so as not to include them automatically.

I crave your thoughts on the best template to use for this.

I will create a form for submission of any new designation/award, and key leaders will have access to edit the sheet, or at least their portion of the sheet. I'd also like to be able to send an email reminder to anyone who has any expirations coming up.

I look forward to your ideas! Thank you!

Mike Wilday · 12/29/20

Smartsheet seems like a viable solution for you to complete this.

I would make your primary column be the hospital/affiliate names.

I would also add a dropdown column for award which would hold the name of award.

I would also add a dropdown column for award type which would hold your four categories.

I would also definitely add an award date column and an expiration date column.

Then I would put a column formula to count the number of days till expiration. =Today() - [Expiration Date Column Name]@row

In addition to those I would probably also make a separate spreadsheet for documenting the name of each award and a definition of those awards create another column that does a vlookup formula to find the definition of the award and bring it into your sheet.

For more on VLOOKUP see:

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

From this you could create a separate dashboard for each hospital which could display a filtered report for each type of award.

Mike Wilday · 12/29/20