Display most recent entry/value (by date and time) into a dashboard

kimkummer

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

Kelly Moore

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