Dashboard - How to Add Dynamic Editable Text on Dashboards
Hi,
I am working on creating a Dashboard where the Project Manager (PM) can input their Project Summary, which will then be displayed on their Dashboard. I understand that I can create a Metric Widget, connect it to a specific sheet, and point it to a particular cell. However, I need to ensure that this setup remains dynamic. In other words, how can I ensure that the most recent updates are reflected on the Dashboard? We want this sheet to track the weekly summaries and ensure that the latest updates are always visible on the Dashboard while maintaining a list of past summaries. Thank you in advance.
Best
DA
Best Answer
-
@Devi An The best way to manage this is probably going to be something along the lines of the following:
Use a FORM to create the PM entry. Have one of those fields be a date field where they select the date of the day of entry, That date will then be used as a reference point to determine which row of data in the Smartsheet that the form feeds to base your Dashboard Widgets off of. You'd have to set up some Max Collect, or Max Index types formulas to collect the data based on the Max date entered by that PM, which would represent the most recent date. Assuming the data is just values, this would automatically change the data in the data sheet based upon the newest entries entered.
If you wanted another approach would be to build a report that displays this data nicely on the Dashboard and then publish that report so the PM could see their most recent entry, but by clicking on it would allow them to review previous entries.
That's the direction I'd probably head.
Answers
-
@Devi An The best way to manage this is probably going to be something along the lines of the following:
Use a FORM to create the PM entry. Have one of those fields be a date field where they select the date of the day of entry, That date will then be used as a reference point to determine which row of data in the Smartsheet that the form feeds to base your Dashboard Widgets off of. You'd have to set up some Max Collect, or Max Index types formulas to collect the data based on the Max date entered by that PM, which would represent the most recent date. Assuming the data is just values, this would automatically change the data in the data sheet based upon the newest entries entered.
If you wanted another approach would be to build a report that displays this data nicely on the Dashboard and then publish that report so the PM could see their most recent entry, but by clicking on it would allow them to review previous entries.
That's the direction I'd probably head.
-
Thank you very much. I was looking to create a dynamic field of 150 characters to be fed from a form that a PM can use it on a weekly basis without having navigate to any other place other than one particular form or sheet to update the Highlight of his project aka tweet.
-
@Devi An Devi: Here's the best way to manage this. You will need four separate documents:
Documents
- A sheet to capture all of your PM tweets. You will need to have three columns: "PM Name," "Update Date," and "Update Tweet" (or something along those lines)
- PM NAME: Make this Column2 and change it to a dropdown menu. Add all of your PM names here.
- UPDATE DATE: Make this Column3 and change it to a "Date Type" column and make it "date only."
- UPDATE TWEET: make this your Primary Column, since this only needs to be a text field.
- Using the sheet to collect the data above, make a form that directs back to this sheet. You can provide the same form to all of your PMs and this will function as a repository for all of the data you wish to push out to your dashboard.
- You will need a data sheet designated to act as a place to collect the most recent data to push to your dashboard AND as a helper column location to navigate your desire to have this data change live. I will include the formulas below.
- Your dashboard. This will direct back to your data sheet in step 3.
For the Data Sheet your first step will be to create Helper Columns to allow you to navigate identifying the most recent data a PM has put an entry. To do this you will need Three Separate Columns:
NAME OF PM - In this column list every PM that you need to collect an update tweet for. Please list one PM per row. It should look something like this:
Last PM Entry Date - The second column needs to be a date column, but do not select "Date Only" as you are going to need to enter in a formula to collect this date. The formula is as follows:
=MAX(COLLECT({Update Date Column From Your PM Data Sheet in Point 1 Above}, {PM NAME Column From your PM Data Sheet in Point 1 Above}, [NAME OF PM]@row))
What This formula will do is reference the entire Date Column on Your Sheet that collects the submissions rom your form and pull back the MAX, or most recent date for the name of the PM you assign to this search. Make sure for the two parts of the formula above that reference the other data sheet (Point 1 above), that you select the entire row as the reference, so as new data populates it catches it).
PM UPDATE TWEET - The Third column on this data sheet will be the equation that takes your date you've identified as the PM's most recent tweet date and then goes and finds the Tweet that matches both the PM name and the Date you've identified. That formula looks like:
=INDEX(COLLECT({Reference the entire UPDATE Tweet Column from your data sheet from point1}, {Reference the PM NAME Column from your data sheet in Point 1}, [NAME OF PM]@row, {Reference the entire Date Column from your data sheet in Point 1}, [Last PM Entry Date]@row),1)
So your data might come back looking like this:
Then you simply build your dashboard and have your Text Widgets Just be a fixed name for your PM and then the actively changing widget point back to the tweet. You could also add a widget that points back to capture the most recent date.
I hope this helps!
- A sheet to capture all of your PM tweets. You will need to have three columns: "PM Name," "Update Date," and "Update Tweet" (or something along those lines)
-
@Devi An Hey, Devi! I just wanted to see if my other response was able to help you resolve your larger request?
