Dashboard Formula for Projects Within Date Range
Hi there, I'm trying to add a widget to my dashboard that tracks how many projects were initiated by year. In the dashboard metrics sheet, I reference the project intake sheet but cannot get the correct formula to count the number of projects by year. For reference, on the project intake sheet, the due date column has the full date (mm/dd/yy). I could be doing this completely wrong but thinking if anyone has experiencing creating a similar formula while referencing another sheet, perhaps you have some insight.
Thanks so much!
Answers
-
Hi @HProulx,
as you are trying to calculate something for the widget and widgets only show data, you need to provide the desired info in a sheet or report.
I suggest setting up a metrics sheet or some additional columns in the intake sheet where you count projects started by year and then use this as source for the widget.
Does this make sense to you?
Hope this helps!
Stefan
PS: You write "due date column" but probably mean "start date"?
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Hi there, yes I have the metrics sheet set up and all my other formulas work great just can't figure out which one to use for the tracking the jobs by year.
-
Hi @HProulx,
have a look at the screenshot and the formula. For your location you may need to exchange ";" by "," in the formula !
Instead of the reference to the columns in the metrics sheet use cross sheet references to the intake sheets columns. The "start year" column contains =year(start@row) and can be hidden. The yellow coloured cells can be used as source for your dashboard widget.
Somewhat quick and dirty but should work for you; I hope.
Greetings
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!