How can I have daily information reflect on the dashboard?

Paula Cosentino

Each day they want to enter new data to have a running history. I have a form created. My metric widgets, however, pull from the ROW above. I don't want to update the widget each day. I can do this with reports, but they really want the metric widget look. Is there anyway?

Kelly Moore

Paula

Try this for your ED Census Summary Field

=INDEX([ED Census]:[ED Census], MATCH([Field 1]#, [Date Submitted]:[Date Submitted], 0))

You should be able to copy this formula directly into your summary field.

Does this work for you?

Kelly

Kelly Moore

Hey @Paula Cosentino

Depending on how you are pulling 'the row above' (eg Max date <Today()?) you may be able to use the summary fields to collect your data, then pull that summary field into your metric widget. You can find the summary field(s) on the right menu ribbon on your sheet.

Would that work for you?

Kelly

Paula Cosentino

@Kelly Moore Thank you for this start. Will you help me with the formula to bring in the data from the line with the latest date? I got the date to work in summary, but now I want the fields that match that date in the sheet summary. Thank you so much.

Paula Cosentino

Kelly Moore

Hey Paula

I'm happy to help. Please tell me a little more about your sheet. Is there only one submission per date? For instance, is the 5/31 date with [ED Census]=12 the only 5/31 date of the day?

If no, does your sheet contain the system column of Created and/or [Row ID]?

I ask because if there are multiple instances of the same date (for instance 5/31/22) then the formula must provide smartsheet with a way to know which one is the very last instance. There are a few work-arounds and using the system column(s) are the most straight-forward. No worries if you don't have these and can't have these - there is still a work around.

Kelly

Paula Cosentino

HI Kelly,

yes, only one submission daily

Kelly Moore

Paula

Try this for your ED Census Summary Field

=INDEX([ED Census]:[ED Census], MATCH([Field 1]#, [Date Submitted]:[Date Submitted], 0))

You should be able to copy this formula directly into your summary field.

Does this work for you?

Kelly

Paula Cosentino

ohhhh, now I'm so excited. Thank you for helping me!!

Kelly Moore

Awesome! I love that feeling!

Glad it worked for you.