top of page
21414553-7dbf-447c-a5d1-6047d0b9ec47 (1).png

The Challenge

CCSEM was keeping track of all grant information in disparate Excel documents. With information scattered, it was difficult for the finance and leadership teams to obtain any sense of order regarding grant status. CCSEM's CFO wanted to implement a Smartsheet master suite that kept track of the grant process, from submission to approval and all the tasks in between. This meant CCSEM would need an overal grant dashboard that provided an all-in-one, automated view of grant information across all of CCSEM's operations. Moreover, the CCSEM team wanted to implement a master grant data sheet that included linked unique IDs between other sheets.

The Solution

To begin, the CCSEM team and I started by creating an outline for how the CCSEM Smartsheet dashboard would function. Together, we built the following capabilities outline to get us started:

​

The CCSEM Dashboard operation is as follows:

  • Grants and Grant Opportunities are submitted

    • by a Form

    • to the Grant MetaData Sheet

      • Appended to the bottom

      • Given a unique, sequential 5-digit Grant ID

      • The Grant ID is included on ALL information related to that Grant

  • When a Grant is Awarded

    • Grant Status is changed

    • Grant Tasks are added

      • By a Form for each Grant Task

  • Anytime that Grant Info is obtained

    • Grant Info can be added

      • By a Form for each Info Item

  • On a monthly basis, Accounting will enter the Financial Status for selected grants

    • By a Form for each Grant ID

    • In reality, this would be done by a spreadsheet upload

  • The Dashboard then largely consists of:

    • A series of Reports

    • Sorted by Grant ID

    • Filtered to show only the Fields desired

After completing the discovery to spell out the dashboard's high-level features, it was time to start building some of the underlying sheets, a crucial component for all Smartsheet dashboards. Starting with figure 1, the CCSEM team and I built a master sheet for all grant information. As indicated in figure 1, field permissions were of great importance in developing this sheet. CCSEM's CFO did not want the entire team to be able to edit the fields in the sheet. Only fields such as contacts and grant status field were unlocked for editing by shared users. Information in this master report would then be manipulated into different views on the dashboard.

Figure 1

Grant Master Data Sheet

To streamline grant submission, a Smartsheet form, shown in figure 2, was developed to prompt users for the necessary information to kickoff the grant approval process. The key advantage of using Smartsheet's forms is that information automatically is entered into the master data sheet and each submission generates a unique ID. Moreover, the grant form prompts users to select from a predetermined list of choices for some fields to help keep data clean.

Figure 2

Grant Opportunity Entry Form

In figure 3, the final grant dashboard is shown. There are a few key elements to highlight in this dashboard. First, this dashboard is designed to provide specific information related to grants within each "boxed" module. In other words, each module on this dashboard speaks to a different, specific element about the grants - like grant owners, grants awarded, grants approved, or grant tasks. This design helps point the viewer to a specific piece of information they might be looking for about CCSEM's grants. Next, adding web widgets that allow for grant opportunities and task submissions via a form helps streamline the grant procedures, allowing the viewer to make updates to grants without leaving the main dashboard. The grant opportunities module is filtered by the user allows the dashboard viewer to see only grant opportunities that relate specifically to them. Finally, the dashboard includes another web widget for feedback, allowing any dashboard user to submit a ticket for additional dashboard features as necessary.

Figure 3

CCSEM Grant Dashboard

In keeping track of tasks, the task management sheet connects tasks to the appropriate grant by using the grant ID. Using Smartsheet's column formula feature, the "Grant ID" column is written with a VLOOKUP formula that searches for the grant name on the task sheet in the overall master grant data sheet and pulls back the appropriate grant ID. By associating tasks with the appropriate grant ID, the dashboard can report on multiple tasks for multiple grants in a singular module.

Figure 4

Grant Task Sheet VLOOKUP Function

Other Case Studies

bottom of page