How do I auto-save a sheet without opening the sheet?

Lawrence West

I use conditional formatting. When I review the dashboard, the formatting doesn't update until I go to the sheet and save. I would like the sheet to save automatically when conditional formatting makes changes. Thank You.

Paul Newcome

Following to see other's suggestions...

Genevieve P.

Hi @Lawrence West

Would you mind explaining a bit further? How is the sheet being used in the Dashboard, is this through a Published link via the Web Content Widget? What are your conditional formatting rules (what criteria are they based on), and how is that criteria being updated?

It would be my assumption that the update which causes the conditional formatting to run re-freshes the sheet so you shouldn't need to go in and open it up.

For example, I just tested submitting a form to a sheet that contained a certain status, which the Conditional Formatting rule would then make the row a certain colour. After submitting the form I checked the Dashboard, where a published view of my sheet was displayed. This published view showed the new row submission with the conditional formatting rules applied.

If this is not your scenario, it would be very helpful to see some screen captures of your dashboard and conditional formatting rules set up, but please block out any sensitive data.

Thanks!

Genevieve

Paul Newcome

@Genevieve P I can's speak for Lawrence, but my particular situation is a Report Widget on a dashboard.

Here's how I have it set up...

Master sheet has conditional formatting in place based on checkboxes being checked. The checkboxes being checked also drive the report. So if the box is checked the conditional formatting should show "red" and the row should be displayed on the report.

When I opened my dashboard, the rows were pulled correctly based on the boxes being checked, but the conditional formatting was not in place.

I did not go to the report to check there. Instead I went directly to the Master Sheet to double check the conditional formatting rules.

When I opened the sheet, the save icon immediately became "un-greyed" out. Unfortunately the rows in question were too far down to be on the screen to see what they looked like as soon as the page opened.

I went ahead and saved then scrolled down to the rows which now showed the appropriate formatting.

I went back to the dashboard and found that the Report Widget also now shows the appropriate formatting.

I would think that if the condition is met to be shown on the report is working then (since it is the same condition) the Conditional Formatting should also be working.

I haven't had this problem before just the other day using the same sheet/report/dashboard in question.

@Lawrence West Does that sound similar to your situation?

Lawrence West

Yes that is very similar. I have conditional formatting that strikes through text or changes text color based on conditional formatting and it does not update until i physically go to the sheet. Once I open the sheet and it is saved, the dashboard formatting displays properly.

Genevieve P.

@Lawrence West

How are the changes being made to your sheet?

For example, is the text struck through or the colour changed based on a date? Or does this change due to new rows added in, from a form?

I'm wondering if there may be a set up that requires the sheet to recognize the day, like with the TODAY function (see here) in order to update the Conditional Formatting.

It would be very useful to see Screen Captures of your sheet in Grid View, the way you have your Conditional Formatting set up, and also how this appears in your Dashboard. Would this be possible to provide? Please block out any sensitive data.

Paul, I'm not sure what happened in your instance - if the Report was pulling in the correct rows, it should also be pulling in the correct formatting for those rows as well... let me know if this is a consistent pattern. I've tried to replicate the behaviour by having the new rows come in through a form submission but it updated the Dashboard & colouring as expected.

Paul Newcome

@Genevieve P I'll keep you posted.

That particular report is only pulling rows that have a very specific issue with them, so (fingers crossed) it remains blank most of the time.

The source sheet is also accessed anywhere from once or twice a week to a dozen times in a day by a total of 6 people (myself included) depending on our workload so even if there is something for the report to pull that isn't getting corrected when the sheet is opened, it is exceptionally rare for this particular scenario to occur.

The Conditional Formatting is driven by the same checkbox that the Report pulls from.

The formula in the checkbox is date based, but it is compared to a date being cell linked in from another sheet (my own version of an automatically updating TODAY() function). This cell link updates daily and runs A LOT of metrics. All of the metrics update daily. All of the reports update daily. It is just the Conditional Formatting that seems to have been an issue this time.

I may set up some controlled tests to see if I can replicate it.

Lawrence West

@Genevieve P the conditional formatting is looking at a complete date to be filled in. If the date is not fill in and the date is in the past, the text is red. If a completion date is present, the text is struck through. I do not current have any formulas using the TODAY() function in these sheets but i can add easily enough. Can I use one TODAY() formula at the top or does each row need to have it?

Genevieve P.

@Paul Newcome Yes, please keep me posted! I would like to figure out what's going on - if it's expected or not (doesn't seem to be... based on your description of all the updates that continually happen).

@Lawrence West No, there's no need to use the Today formula... however I am thinking that the dates in your Conditional Formatting rules act in the same way, which is why I mentioned it:

"Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:

• You open the sheet and save it
• You add a new row to the sheet from a form submission
• You make changes to the sheet from an update or approval request
• You update the sheet by way of a report
• A linked cell updates the sheet
• You open the sheet being referenced in a cross-sheet formula

The following will not update formulas that are using the TODAY function to the current date:

• A report looking at the function in the sheet is opened.
• A dashboard showing information from the sheet is opened."

My thought is that potentially these Conditional Formatting updates aren't pulling through until the sheet is opened because it needs to recognize today's current date, in order to then apply the formatting changes.

I could very well be wrong though, so you may want to reach out to Smartsheet Support if the behaviour continues. Be sure to share screen captures with Support, along with sheet names and the link to this Community thread.

Paul Newcome

I tried a very simplified version of the setup, but it didn't replicate the issue. Everything seems to be working as it should now. I will keep an eye on it.

I do feel like I have seen this before in the past though either in my own dashboards or in the Community (I can't remember which).

Lawrence West

I think, based on your information, I will create a master sheet and create a cell link between that sheet and every other sheet i have. I can update that sheet which should, according to my understanding, refresh all linked sheets. Not thrilled with the solution but also not difficult.

Paul Newcome

@Lawrence West I have that set up on all of my sheets, and I still ran into the issue outlined above. Hopefully it was a rare glitch on my end and the linking will clear things up for you.

I actually use Zapier to add a new row to the "Master Link Sheet" so that I don't have to update anything manually. That is all I am using Zapier for, so it still falls within the free version.

I have a date type column with

=TODAY()

in it on the "Master Link Sheet". Then when Zapier adds the new row TODAY() is updated. That's the cell I link to on my other sheets. Then in the formulas on my other sheets I can reference the linked cell instead of using the TODAY() function which essentially gives me an automatically updating TODAY().

Just a little suggestion on how a "Master Link" could be leveraged to (hopefully) make things a little easier for you.

Lawrence West

@Paul Newcome I may go to using the Zapier. I just created the master and I put a link at the top of my dashboard that says refresh dashboard which takes me to the sheet and i can save. I am going to test that for the next few days to see if i run into any issues. Thanks for the advise.

Paul Newcome

Happy to help. Another option that would save you from going to the dashboard and then going to the sheet and then saving would be to set up a recurring Update Request on the master sheet that sends you an email every day. You can open up the update request from the email, change a field, then submit and it will refresh the sheet.

You could even create a form that has a default value in some field and submit that daily as well. Just open the form and click submit. If you put the form in a Web Content widget on the dashboard, you could submit it directly from there and then just refresh your browser tab.

There are actually quite a few different ways to accomplish refreshing depending on your needs and how much work you do or don't want to do.

Feel free to revisit if you would like to explore some of the other options or need assistance setting up the Zap.

Hitesh Wadhwa

One suggestion on this topic... I simply did 'Auto-refresh' to 1 min, instead of 10 mins... hope that will solve the problem, after 1 min?

Paul Newcome

@Hitesh Wadhwa I'm not sure I follow. Auto-Refresh will only work when a sheet is open.