Auto Generation of Crossreferenced tables

Cruz Ortiz

Topic: New Sheet Row Generation with selected data from a source Sheet with data linked. 

FYI: I posed this @ Product Ideas too.

If you are used to ERP linked data tables you may have explerienced the need to get similar benefit in Smartsheet.

We are in the need to generate a new row in a "destination sheet" triggered from a "source sheet" based on specific criteria met at the source. That new row should have several fields linked to the original source sheet and kept linked.

Problems are:

1. Workflows only allow to COPY the entire row and the destination row will never be linked to the original sheet data.
2. Data Shuttle export and import back into Smartsheet is a terrible workaround.
3. Only way we know, is a workflow @ source sheet to trigger a webform going into the destination sheet where we need a manually enetered "Key" typed to pull other desired data from the same source sheet.

Ideal solution:

1. Workflows: Hability to copy specific fields from a source sheet into a destination sheet.
2. Hability to keep data linked from copied rows (if desired/needed).

Thoughts?

Willing to help?

Lindsay Whitbread

Hi,

I hope I've understood your question properly, but it sounds like the solution that you need is to run that COPY automation that you mentioned, which will include some kind of Key. Then, in your destination sheet, you can create INDEX-MATCH formulas that reference back to the source sheet. If you set those formulas up as Column Formulas then when the COPY automation runs, it will not overwrite the data in those column formulas, and you should get the outcome that you're after.

I hope that helps!

Lindsay

Cruz Ortiz

@Lindsay Whitbread

Thanks for the feedback however "copy the whole row" does not help as the 2 tables will have its own different content but only linked by the key and few shared columns. Exact same concept as ERP tables.

As mentioned before, Smartsheet should allow to copy only desired fields -columns- and not the whole row. That is why I added this request to the Product Ideas dashboard.

I still hope to find an easy way to handle this within Smartsheet.

Thanks

Andrée Starå

Hi @Cruz Ortiz

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that 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!

Lindsay Whitbread

@Cruz Ortiz with the copy row automation, yes it drags over all of the columns that you don't need, however, in most cases you can just hide those columns and have the sheets still function the way that you need.

@Andrée Starå I liked your idea for providing a 'clean' row copy! Do you find that the method is reliable? I've sometimes found that automations with a few moving parts can fail from time to time.

Cheers,

Lindsay

Cruz Ortiz

Thank you @Andrée Starå & @Lindsay Whitbread

Thank you both for your contributions.

Either way, I am sure you both coincide, it would be nice that Smartsheet developers create a partial -fields selective- copy rows function. Even nicer, to implement a feature to get sheets automatically linked, same as ERP does. Maybe something more users see the beneffit and generate demand for it in the future.

Now, going to the suggestion from @Andrée Starå

I am a little confused as you mentioned 2 helper sheets + 1 main destination sheet = 3 sheets...?

Please take into account the goal is to obtain only one destination sheet with few selected fields (columns) driven by a "key" field. At the end we face the same issue, how to automatically get rid of undesired fields at destination sheet. If you were willing and available to meet, please send me a message @Cruz Ortizto cruz.ortiz@rimex.com so we may arrange a quick Teams or Zoom meeting.

Thank you very much again. Cheers.