Need help with a index matching formula or next best alternative

SiddV47

Hello,

Our dept's role is to facilitate visitors (overseas medical faculty), who are invited by the respective clinical departments chairs to our hospital.

There is a lot of paperwork involved between the host department, our dept and HR. We are therefore leaning on document builder feature. But one thing, the good community could help me is the setup the index/ matching formula or any other better alternative.

Here is the ask:

When an intake form (Smartsheet form) is submitted (See IMAGE #1 for the sheet it populates), we would like to view the following task lists (the grey color tasks see IMAGE #2) below the newly submitted entry (see in IMAGE #1). That way, the project coordinator may start processing the paperwork and follows the order of the forms hierarchy to complete the task.

Will INDEX MATCHING formula work here? Happy to look into other alternatives.

IMAGE #1

IMAGE #2

Thank you!

Michelle Choate 2

No - an INDEX/MATCH formula is for when you have information you need to add in the same row with one matching source column (such as name being the same in both sheets, and you want to bring in the email address or phone number from another sheet).

To do what you are asking is only accomplished by copying the rows from the source (IMAGE #2) to the destination (IMAGE #1). You could set up a workflow that alerts you when a new row is added so you can manually copy all the rows from the source, because only if they are moved as a group will they retain the same hierarchy. There is no automation you could set up in the source sheet that I know of that would move them as a group because automations are meant to look at the sheet line by line and run line by line, not as a group.

Paul Newcome

@Michelle Choate 2 The reason the Copy Row automation may not work is not because of multiple rows / hierarchy issues, but copying a parent automatically grabs the child rows and copies them with.

The three main challenges I see right off are in the main structure and in the trigger.

The main structure includes predecessors. These will not update when the rows are copied from the source. Copying the rows will also not automatically indent them under the form submission row.

The trigger also cannot be based on a cross sheet reference. This means the copy trigger would have to be date based. We could write out a formula to see if there are any forms submitted that do not yet have child rows, but the absolute best we could do would be an hourly copy which limits the process to only having one form entry per hour max. You also still run into the issue of the copied rows not automatically indenting under the newly submitted for row.

But…

If we structure the form sheet to also contain the task set, we can use some formulas and helper columns to solve this.

Here is a link to a thread that should have a solution for you.

https://community.smartsheet.com/discussion/108432/auto-create-child-rows-without-bridge-or-api