Display most recent entry/value (by date and time) into a dashboard
Hello,
I have a form that we have team members fill out twice per day. I want to ensure that the most recent values in select columns are displayed to the dashboard based on most recent entry for that day. I have a second metrics sheets that is trying to pull this from the original sheet, but unable to find most efficient way for this to auto update with each new entry based on time/date or other field.
any guidance would be helpful.
kim
Answers
-
Hello @kimkummer
One approach is to add a helper column to your source sheet (a checkbox column will work. This is Sheet1) to flag the most recent date. You can push this column out of the way and hide once the column formula is added. Note: I wasn't sure if you were keying off of Created or Modified field. If you use Modified, correct this formula.
=IF(Created@row = MAX(Created:Created), 1)
You can then use this checkmark as criteria in your cross sheet references. For example on the metric sheet:
=INDEX({Sheet1 column you want}, MATCH(true, {Sheet 1 Helper checkbox}, 0))
remember you must create your cross sheet references manually. You cannot copy paste this formula with also creating the references
if you have multiple criteria that filters the information, use the COLLECT function
=JOIN(COLLECT({Sheet1 column you want}, {Sheet 1 Helper checkbox}, 1, {sheet 1 next column}, criteria2))
*Note: If you are collecting a number, use the MAX/COLLECT instead of a JOIN/COLLECT to prevent number from behaving as text
Does this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!