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.
Answers
-
@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) -
Thanks for the recommendation, Phil I really appreciate it. I'll give it a go.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!