How can I have daily information reflect on the dashboard?
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?
Best Answer
-
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
Answers
-
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
-
@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.
-
-
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
-
HI Kelly,
yes, only one submission daily
-
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
-
ohhhh, now I'm so excited. Thank you for helping me!!
-
Awesome! I love that feeling!
Glad it worked for you.
