Smartsheet: Update Multiple Sheets
Updating a Sheet with new functionality takes time. This is true, despite what your supervisor may think. Updating 10 Sheets with the same functionality? More time.
This post won’t show how to eliminate all of the time, but it should save some.
A Simple Example
I have a Project Master Sheet (I avoid using Smartsheet’s Templates for this function and avoid using the term ‘template’ for my Master Sheets). Over the course of the past year, we have rolled out many projects, of which 17 are still in progress.
Someone on the Team has made a very sensible suggestion, and now we want to update the Master and also the 17 projects in progress too.
The change is simple, we want to add two Date columns, the second with a formula based on the first new column.
Build a New Sheet
I could update the Master directly here. I often do. Sometimes I don’t and instead build a separate “Updater” Sheet.
Just two reasons:
- There may be changes in the project sheets that do not reflect in the Master. The Primary column is copied from the source sheet to the destination sheet regardless of column name. Other columns are matched by name. New unmatched columns are added at the right of the destination sheet. There may be extra clutter after the update. That’s just more work.
- Some new functionality can be tested in the smaller Updater Sheet before it is rolled out, without impacting projects in production.
There are other reasons, but let’s move on.
I start with a Project Update Sheet that has only a few columns, maybe only the Primary column and then build it out.
- Add columns – for this example the [Order Date] and [Order Date + 30] columns are added. I verify that these column names are unique, at least in the project sheets of concern.
- Add functionality (like formulas). Once tested, the formula is ‘commented out’. I use the exclamation point (“!”).
Smartsheet copies over the Display Value for formulas, not the formulas. (This is probably a good thing, there is extra processing required to make sure the destination sheet isn’t being given an formula that will cause an error). By commenting out the formula, the formula will be preserved during the copy.
- Color the row with some background color.
- Add unique text to other columns.
Steps 3 and 4 are just sanity checks when the row reaches the destination sheets. New columns will be added on the right side of the Sheet. The text and color will key me in immediately if something is amiss. Too many new columns? Not enough? Something did not go as expected.
- Select the row and “Copy to Another Sheet” from the Row Menu. Currently, only one destination sheet can be selected at a time. I’ve submitted a Product Enhancement Request for that to be changed. You should too, if you agree it would be useful.
- Test the destination sheet. Work out any kinks in the new functionality or in the update process.
This includes removing the “!”. I use “!” instead of a single quote ” ‘ ” because I can find and replace it easier.
- Repeat (5) and (6) until done.
- Depending on how (6) went*, I will either do: the rest, a batch of sheets, or one at time. If I had to make changes on the destination sheet side, I will be more methodical. If not, I will be more trusting that things are OK.
Further Improvements and Closing Thoughts
What, you thought there would be more? I said this was a simple example.
For a 12 sheet update, this process cuts my update time by (a lot). I would like to be able to say “80%”, but I didn’t keep metrics on how long the old way took other than “too long” and I don’t have time to go back to do it the slow way once to figure it out. But it is enough that I keep doing it the new way.
I hope it saves you time too.
If you like this post, please “Like” it.
If you are new to Smartsheet and want to check it out, click here. If you have questions about this post, add them below. If you have questions about something else in Smartsheet, post on the Community. I occasionally stop by to answer them and so do a lot of other talented people.
*I hope it goes well.