Information distribution system in Smartsheet

The problem situation consists of the existence of a main sheet, called "0. Main sheet (source)", in which important items are listed, accompanied by their respective IDs.

You want other sheets to contain some of the information from the main sheet, but not all of its columns, just some specific columns. And it is important that the information is updated without human intervention in the target sheets "2.1 Test Sheet (target)" and "2.2 Test Sheet (target)" as soon as a new row or new information is entered in the sheet "0. Main sheet (source)".

Solution description:

1)In the main sheet, "0. Main sheet (source)", we create a "copied" column that identifies the new rows that have been inserted into the main sheet and that have not had their information copied to the target sheets.

The formula basically identifies whether there is a row ID in the target worksheets and if there is, it selects the checkbox; if there isn't, the checkbox is deselected.

2) There is an auxiliary spreadsheet that collects the ID's of the new rows that are inserted into the main spreadsheet

The formula basically collects the IDs that have not yet been copied to the target worksheets. To do this, it uses the criteria that the main sheet's ID has a value and that the main sheet's "copied" column is unchecked.

3) In the auxiliary spreadsheet there are hourly automations that copy the rows to the target spreadsheets.

4) Finally, in the target spreadsheets, simply create INDEX MATCH formulas for all the information you want to collect from the main spreadsheet.

This way, the target spreadsheets will always be up to date with the latest information uploaded or updated in the main spreadsheet.

Information distribution system in Smartsheet: Conclusion

In summary, when we finished implementing the information distribution system in Smartsheet, we noticed that the target spreadsheets updated automatically. The "copied" column in the main spreadsheet identifies the rows every hour and transfers them to the target spreadsheets.

What's more, the effectiveness of this approach ensures that information is constantly updated and aligned between spreadsheets.

And since you've come this far, how about exploring more tips on Smartsheet?

Keep discovering new Smartsheet features and making the most of the platform's potential to boost your productivity in the same way. Check out our blog >>

TaskIT Smartsheet Consulting

We provide consultancy, implement, train and support your team on Smartsheet

See Full Bio