Any ideas on an easy way to capture data on a rolling monthly basis and show on a dashboard

We are capturing different types of statistics for multiple different subject areas every month and need to:

a) easily get people to update the data

b) easily show it on a Dashboard

I've tried multiple different ways of sheet design but I keep coming up again blocks - ie either data can't be shown in a graph or the workaround causes a lot of effort linking sheets at the end of each month.

If anyone has any ideas on the best way to report via a dashboard on rolling monthly data, I would be eternally grateful!

Here is what I've tried so far…

1. Vertical Month/Horizontal Subject/Status in Cell

2. Vertical Subject/Horizontal Month/Status in Cell

3. Filled in via Form adding each subject, month, year, status - row per subject for each month.

Find more posts tagged with

Sheets

Reports

Dashboards

Accepted answers

All comments

Philip Robbins

@Caroline Elliott I would recommend a combination of 1 and 3. Use 3 to receive monthly submissions on each subject then pre-configure sheet 1 with your Month/Year list and in each subject column use an INDEX/COLLECT formula to pull back the relevant status from 3.

Assuming you know how to use cross-sheet references, your formula in the Subject 1 column in sheet 1 would be:
=index(collect({Sheet 3 Status},{Sheet 3 Subject},"Subject 1",{Sheet 3 Sleeper - Month year},[Month year]@row),1)

Caroline Elliott

Thanks for the recommendation, Phil I really appreciate it. I'll give it a go.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.