Using a Master sheet for various department needs

andrew.taylor37161

Hi All

We run a Strata (Units Plan) business that has a few different functions and these different areas have different needs and use Smartsheet in different ways.

We have automated the process of gathering our base data (the buildings we manage) from other operating systems which forms the basis for a large proportion of our working Smartsheets. In some departments, we have a need to add additional columns of manual data and also store related documents in the attachments, plus we use the comments as a record of due diligence.

We are a rapidly growing business and we are constantly adding new buildings and occasionally lose some. The data we download for our base info is changing accordingly.

The issue is that we will receive updated data that is in a different order as it downloads from the various platforms in accordance with the Unit Plan/Strata Plan number and then this makes the manually added data, comments and attachments to be on the wrong row. We can address the lost buildings on row placement by including the inactive buildings, but don't always win business in UP/SP number sequence.

I know that Smartsheet cannot auto sort and was wondering if anyone has any suggestions including outside of Smartsheet.

Andrée Starå

@andrew.taylor37161

Happy to help!

• Would it work to add multiple so-called helper columns for the added columns so they would show the information on the correct row using INDEX/MATCH?
• Another option could be to see if Bridge could be set to trigger sorting the information.

Would any of those options work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@andrew.taylor37161

Excellent!

You're more than welcome!

Let me know how it goes!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @andrew.taylor37161

I hope you're well and safe!

I'd need more information to give a better recommendation, but here are a few things that could improve the process.

Is everything in one sheet today or split out into different department sheets?

• Develop a so-called Import Sheet that is connected to the other sheet(s), and as long as we have a unique value that we can match, we can collect the information needed.
• Premium App, Data Shuttle, used to automate the Import to Smartsheet from the other systems.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

andrew.taylor37161

Hi Andree

Thanks for reaching out.

To explain, we are using Bridge (with about 10 workflows) to get the data via an API from our Strata financial program. We use data shuttle to move this data to several Smartsheet Datatables (it is a huge amount of base data as we have 170 buildings and 13000 units and each unit can have up to six people connected to it and it does not fit on a sheet), then there are a "connections" with the Datatable to populate the various master sheets (occupant data, building data, financial data).

We also use power automate to collect information from other programs as well and data shuttle to add to Datatables.

Yes, in the example I have described, we have different departments that use the base data (via links) generally in the first 15 columns (as that data is standard on each of the sheets) to their working sheets.

As stated, different departments have different columns that are populated manually for work we do that cannot be done in the main programs and because I cannot find an auto sort solution within Smartsheet and as described earlier, we will have the Bridge (and other data sourcing methods) information coming through and this data will reflect any additions/losses we have have won/lost.

All data received will be in Unit Plan number and each Unit Plan will have it's own row (we cannot change the downloads order) and I have attached a spreadsheet image that may help explain the issue I am trying to give.

It is just that as each row will be fed out to the masters via a link, the additional data that has previously been added will be reflected on the incorrect row/building.

Row order example.JPG

Andrée Starå

@andrew.taylor37161

Happy to help!

• Would it work to add multiple so-called helper columns for the added columns so they would show the information on the correct row using INDEX/MATCH?
• Another option could be to see if Bridge could be set to trigger sorting the information.

Would any of those options work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

andrew.taylor37161

Hi Andree

I will investigate the Indexing solution further. Thank you

Andrée Starå

@andrew.taylor37161

Excellent!

You're more than welcome!

Let me know how it goes!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!