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

Caroline Elliott

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.

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.