Multi-sheet Compilation

Pestomania

I use a large excel sheet that has 9 tabs (screenshot below).

I have created a smartsheet grid named "BIA All Data" (Excel version attached). All of the column names begin with a prefix that matches fields on one of the tabs. Rather than sending this excel sheet to individuals and asking them to fill in the tabs and then transcribing this data into Smartsheet, I am trying to find an easier option.

Is there a way to send a link to Smartsheet grids that can look like the original excel sheet (screenshot below) and once an individual has filled in the data, it will auto-import into "BIA All Data"? I cannot figure out how to create a grid and data shuttle combo that would allow this and a form does not work since it will not allow me to put rows in that are specific examples (quality - primary, secondary, etc.).

Any recommendations for this project are greatly appreciated.

BIA All Data.xlsx

Paul Newcome

You should be able to use Data Shuttle for this.

Pestomania

Hi @Paul Newcome,

I am still struggling to get all of this to work in line like I wanted it to do. I am going to break everything up via different sheets, but because I want to use one form, I think I am going to have to do the following type of workflow. Based on the field "Parent Process", it would indent underneath that process field.

I have a formula that can do numbering such as:

• Task 1
  • Sub-Task 1
    • Third Party 1

I can do that, but it's the indentation that I would really like and I would like to understand what types of formulas would help to culminate it together. In some cases, one sub-process or equipment will be used for multiple parent processes, so I would like to see it show that.

I might be able to set it up in Excel for a better example if that would help.

Paul Newcome

You are getting into the premium add-on Bridge or the API to be able to automatically manage hierarchy.

Pestomania

Thank you for that information. I will ask our team if we have either, but don't suspect that we do.

Pestomania

What about just managing the data to pull it over into one table? I can manage it separately if needed during that (without the indent).

Paul Newcome

As long as you have business licenses or higher, you have access to the API.

Getting all three sheets together… I may need some more clarification on how these sheets are being populated.

Pestomania

These sheets are being populated manually. I have uploaded a "proof of concept" excel sheet that shows hopefully what I am wanting to do. I understand that I may not be able to do the hierarchy but conceptually this is what I am hoping to do. Take the data and create a tree from each of the sheets.

Proof of Concept.xlsx

Paul Newcome

How are you indicating which (for example) sub-processes go to which parent process?