Display current month's data on dashboard
I have developed a metric sheet and would like only the current month's data to show on a dashboard chart without having to manually select it each month. I have seen several posts about this but can't make it work for me. Below is my sheet info. How would I get March to display currently?
Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9
January 1 1 2 3
Feb 43 65 23 28 7 3 40 4
March 1 5 29 34 3 5 7 4
April
May
June
July
August
September
October
November
December
Thanks!
Best Answer
-
You would need another row (I suggest making it a parent of all of the base metrics), and then you would use a formula in each column such as
=INDEX(CHILDREN(), MONTH(TODAY()))
Basically we use the INDEX function to pull from the child rows and then we use the current month to specify which row from the children rows we pull from. Drag this across the rest of the columns in the parent row, and you should have your current month metrics which can now be referenced on your dashboard.
Answers
-
You would need another row (I suggest making it a parent of all of the base metrics), and then you would use a formula in each column such as
=INDEX(CHILDREN(), MONTH(TODAY()))
Basically we use the INDEX function to pull from the child rows and then we use the current month to specify which row from the children rows we pull from. Drag this across the rest of the columns in the parent row, and you should have your current month metrics which can now be referenced on your dashboard.
-
Worked like a charm! Thanks so much Paul! :)
-
Happy to help. 👍️
-
Hi @Paul Newcome , Could you please provide me with an example of the above question? rather than the overall formula
Thanks & Regards
Email ID: info@sspmconsultants.com
Did I answer to your question or fix the problem? Please
help
theSmartsheet Community
by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering! -
@khasimbashashaik I am not sure I understand what you are asking for.
-
Hi! I have a similar ask. I have a monthly metrics sheet, but the months are the columns instead of the rows. I'd like a dashboard item to present only the current month's metrics. Any ideas?
-
@SLKennedy You would need a helper column with a basic INDEX function to pull the current month's data into the same column month over month then use this helper column in your charts.
=INDEX(Jan@row:Dec@row, MONTH(TODAY()))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!