This is an AMAZING hack. I'd worked up a similar semi-dynamic dashboard, but it required the User to go into a Sheet and change a variable field there (I literally had every other cell in the sheet locked except that one). So this eliminated a confusing back-and-forth step between the DB and its sheet, and improves sheet security as well. It even opens up the data to former colleagues who can't get into the sheet due to MFA. Thanks so much.
Thanks, Matt. Since we first did this, the data update response time seems to have slowed so if you're impatient it can take up to 5 times refreshing the dashboard for the change to appear, so as long as people know it's not necessarily instant. I've also been testing embedding excel charts as web content and you can use more advanced filters on this, but the challenge I'm working through is the excel update via Data Shuttle in effect creates a new sheet so wipes the chart... I was working on the basis of a dynamic data set from Smartsheet that I data-shuttled to an excel sheet to be able to do this on. If you've got a static excel data set or one that only changes periodically (weekly / monthly) then the extra step to allow external links between sheets that needs authorisation each time should be acceptable for the benefit it brings, but if its a live / daily data feed, that approach isn't practical IMO... work in progress!
Hi, in the second example, in the "QR code item location lookup sheet" are you using VLOOKUP to display values based on the QR code? Or how are those cells configured?
Hi there. To understand the configuration, let's take you through the process; 1. New form submission populates the submission sheet 2. In the submission sheet, there is a column with a formula that check wich item is the latest one for each QR code submitted. (See +-5:20 in the video) 3. In the "QR Code Item Location Look-up sheet", there is a formula (INDEX(MATCH)) that finds the latest item (see +-5:34) and finally 4. We make use of INDEX(MATCH) to display the other values in the Lookup sheet based on the QR Code. Hope that answers your question
When I built this, I found that when I refreshed the input form screen, the reference table (the page with the "1" indicating its the most recent input) would not refresh. When I go to the reference table and refresh, it would then populate indicating my formulas are correct. Has anyone found a work around?
Hi there - the solution seems to have got slower / less responsive over time, but still works, so maybe it's just taking longer... it's a great solution for specific cases where the user knows what they are doing or is happy to be patient for the refresh
This is perfect, thank you so much!!! Everything worked fine, but I have to click on refresh 3 times so it brings the data from the query. Any tips to fix this?
Unfortunately not. It used to work much faster, but now, as you say, it takes a few attempts to refresh, so I usually expect 1-2 minutes to update. So it can be used where you are in control, but with stakeholders who are not familiar with Smartsheet, they risk becoming frustrated, which isn’t what you want. Alternative approach here is to set up the live data connector with excel and embed that in your dashboard, then you can access slices etc that are more advanced from a wider data set
Thank you @@Smartsheetexpert . One last question, can we do similar thing for reporting? In your example, I'd like to filter a report with the month chosen. I couldn't find a way to do dynamic data query and update a report on dashboard to bring the data filtered by the "month" chosen.
Hi, i was wondering, and apologies if it is a silly question but, how do you get the column "Created" to popullate? I played around the form and automation but couldn't get it right. Thank you
The created column is a system column that auto-populates. So when you add in a single look for the 'Created' option and define that as your column type and then try
Can we have a dropdown menu in dashboard? Like if we have several dashboards lets say five dash for five different months. But I wanted to establish the link of all dashboards in one main single dash so that it should allows me to select from my dropdown menu ? So is it feasible?
The same approach applies. You don’t create a drop-down menu as such, rather you use a form with a drop-down. You can then control as many sheets as you wish by linking them to the sheet the drop-down links to.
Hi there, I've tried to do that and almostly did all fine but once I try to add metric widget it doesn't look like yours. I have column names next to each data, it doesn't creat this cool view that you have here
@@Smartsheetexpert there is also amother thing that after submiting a form it takes around a minute or 2 to update data in dashboard, any idea how to speed it up?
@@majkoos unfortunately not without then going into the assets and trying to get them to refresh sooner, which isn't really realistic. We discussed this with Smartsheet recently as it has slowed from when I originally recorded this video. So it has its limitations on that side and that needs to be considered in how it is used... there are many scenarios where this isn't an issue and this solution is excellent, but others where the users would be frustrated by the delay and think it is broken
This is Amazing! wondering how you got the month name in Product Sale Query? The formula I am using is =MID("JanFebMarAprMayJunJulAugSepOctNovDec", ([Month #]@row * 3) - 2, 3), but when I convert this to the column formula it does not allow me to use it in the Form. How do I get the mo name to populate automatically and work in the form?
Sorry for the delayed reply... probably easiest to have a conversation about this so we can see what's not working. You can get in touch via our website and we can set up a call
Hi there - it updates to whatever the last request was. So if multiple people submit queries simultaneously, then it returns whichever was the latest. It’s a workaround for various scenarios, but not for a high traffic / high query dashboard
Hi sir, I have the data in the grid which is in the indented form like parent and child relation. Now, I wanted to have the data in the dashboard but I just wanted the parent part. Can you tell me the possible way. I tried with report applying filters, it is giving me the result but its not feasible. I hope I am making sense here, let me know if you have questions
This is an amazing work around! I’m def going to give it a try!
Great - it's really helpful in a number of scenarios! Let us know how you get on!
This is an AMAZING hack. I'd worked up a similar semi-dynamic dashboard, but it required the User to go into a Sheet and change a variable field there (I literally had every other cell in the sheet locked except that one). So this eliminated a confusing back-and-forth step between the DB and its sheet, and improves sheet security as well. It even opens up the data to former colleagues who can't get into the sheet due to MFA. Thanks so much.
Thanks, Matt. Since we first did this, the data update response time seems to have slowed so if you're impatient it can take up to 5 times refreshing the dashboard for the change to appear, so as long as people know it's not necessarily instant. I've also been testing embedding excel charts as web content and you can use more advanced filters on this, but the challenge I'm working through is the excel update via Data Shuttle in effect creates a new sheet so wipes the chart... I was working on the basis of a dynamic data set from Smartsheet that I data-shuttled to an excel sheet to be able to do this on. If you've got a static excel data set or one that only changes periodically (weekly / monthly) then the extra step to allow external links between sheets that needs authorisation each time should be acceptable for the benefit it brings, but if its a live / daily data feed, that approach isn't practical IMO... work in progress!
That was awesome! Thank you Rich!
Glad you liked it!
Hi, in the second example, in the "QR code item location lookup sheet" are you using VLOOKUP to display values based on the QR code? Or how are those cells configured?
Hi there.
To understand the configuration, let's take you through the process;
1. New form submission populates the submission sheet
2. In the submission sheet, there is a column with a formula that check wich item is the latest one for each QR code submitted. (See +-5:20 in the video)
3. In the "QR Code Item Location Look-up sheet", there is a formula (INDEX(MATCH)) that finds the latest item (see +-5:34) and finally
4. We make use of INDEX(MATCH) to display the other values in the Lookup sheet based on the QR Code.
Hope that answers your question
WOW.. Genious One
Thank you - appreciate the comment!
When I built this, I found that when I refreshed the input form screen, the reference table (the page with the "1" indicating its the most recent input) would not refresh. When I go to the reference table and refresh, it would then populate indicating my formulas are correct. Has anyone found a work around?
Hi there - the solution seems to have got slower / less responsive over time, but still works, so maybe it's just taking longer... it's a great solution for specific cases where the user knows what they are doing or is happy to be patient for the refresh
This is perfect, thank you so much!!! Everything worked fine, but I have to click on refresh 3 times so it brings the data from the query. Any tips to fix this?
Unfortunately not. It used to work much faster, but now, as you say, it takes a few attempts to refresh, so I usually expect 1-2 minutes to update. So it can be used where you are in control, but with stakeholders who are not familiar with Smartsheet, they risk becoming frustrated, which isn’t what you want.
Alternative approach here is to set up the live data connector with excel and embed that in your dashboard, then you can access slices etc that are more advanced from a wider data set
Thank you @@Smartsheetexpert . One last question, can we do similar thing for reporting? In your example, I'd like to filter a report with the month chosen. I couldn't find a way to do dynamic data query and update a report on dashboard to bring the data filtered by the "month" chosen.
Hi, i was wondering, and apologies if it is a silly question but, how do you get the column "Created" to popullate? I played around the form and automation but couldn't get it right. Thank you
The created column is a system column that auto-populates. So when you add in a single look for the 'Created' option and define that as your column type and then try
Can we have a dropdown menu in dashboard? Like if we have several dashboards lets say five dash for five different months.
But I wanted to establish the link of all dashboards in one main single dash so that it should allows me to select from my dropdown menu ? So is it feasible?
The same approach applies. You don’t create a drop-down menu as such, rather you use a form with a drop-down. You can then control as many sheets as you wish by linking them to the sheet the drop-down links to.
Hi there, I've tried to do that and almostly did all fine but once I try to add metric widget it doesn't look like yours. I have column names next to each data, it doesn't creat this cool view that you have here
Probably easiest if you get in touch via our website and then you can send an image etc and we can discuss and help you unlock this
@@Smartsheetexpert there is also amother thing that after submiting a form it takes around a minute or 2 to update data in dashboard, any idea how to speed it up?
@@majkoos unfortunately not without then going into the assets and trying to get them to refresh sooner, which isn't really realistic. We discussed this with Smartsheet recently as it has slowed from when I originally recorded this video. So it has its limitations on that side and that needs to be considered in how it is used... there are many scenarios where this isn't an issue and this solution is excellent, but others where the users would be frustrated by the delay and think it is broken
This is Amazing! wondering how you got the month name in Product Sale Query? The formula I am using is =MID("JanFebMarAprMayJunJulAugSepOctNovDec", ([Month #]@row * 3) - 2, 3), but when I convert this to the column formula it does not allow me to use it in the Form. How do I get the mo name to populate automatically and work in the form?
Sorry for the delayed reply... probably easiest to have a conversation about this so we can see what's not working. You can get in touch via our website and we can set up a call
What if the inquiry was done at the same time using a different laptop and they are looking for different QR codes?
Hi there - it updates to whatever the last request was. So if multiple people submit queries simultaneously, then it returns whichever was the latest. It’s a workaround for various scenarios, but not for a high traffic / high query dashboard
I wonder if you could make the dashboard look at current user To avoid that and also select the last record for that current user
Hi sir, I have the data in the grid which is in the indented form like parent and child relation. Now, I wanted to have the data in the dashboard but I just wanted the parent part. Can you tell me the possible way. I tried with report applying filters, it is giving me the result but its not feasible.
I hope I am making sense here, let me know if you have questions
Can you explain more. Not clear what’s not working / feasible for you
How are you getting the month no. here?
using the month formula =MONTH(date@row)
Hey, how to make the dropdown?
Hi Vrushabh - the dropdown is simply created by embedding a form with a dropdown onto the dashboard