Run a formula on a destination sheet when rows are moved to it

ShaRobinson

I'm migrating a manual process we have into Smartsheet, and keep finding new hills to climb! : )

Essentially the process will result in document generation (PDF) after different team members submit info onto a sheet using Forms.

First, I found a formula solution that worked for me to combine all sheet data (from columns) onto a single row so that I could generate a PDF document from that single row. That's important, and it worked — for a minute..

Each column only has one cell with a value, so I used "=JOIN([column name]1:[column name]19)" to retrieve that one cell value I need from each column. (It's simple, but it works, and I couldn't find another solution to get the one row with all the sheet's data on it.)

Here's where my problem starts (or continues, depending on your outlook!):

I need to apply that "JOIN" formula to a new destination sheet instead (not the source sheet) — after automation copies certain rows to that destination sheet.

The problem I'm having now is that the "move/copy row" action places rows onto the BOTTOM of the destination sheet — which I didn't foresee. So I tried moving my JOIN formula to the top row of the destination sheet instead of row 20, and have it start looking for data at row 2 instead of row 1. It just reads as UNPARSEABLE now. Same formula, just doesn't work from row 1 looking down the column for data?

At the end of the day, I need to be able to generate three different documents at different stages (or different data from same sheet), and all the data for each 'doc' has to be on a same row to do this (and also it's preferred if that occurs from three unique destination sheets —one for each document).

This is the earliest doc in the process, and I am stuck..

Thanks for any assistance!

Sharon

Brian_Richardson

250 columns! Good lord :-)

Ok, so I would do it slightly differently then and leverage the Hierarchy that Smartsheet provides. Hierarchy sets Smartsheet apart from other spreadsheet tools, it's very useful!

In your sheet with the data, instead of writing formulas with fixed ranges like you've done, instead indent the rows with the data underneath your documentation row. Then on the documentation row use

=JOIN(COLLECT(CHILDREN([Detail Column 1]@row, CHILDREN([Stage]@row), "Not Started"), ";")

Brian_Richardson

It sounds to me like you may want to stop the Move Rows and instead develop a "document sheet" that has your three summary rows on it.

If you setup a sheet that has a row for document generation, you can use cross-sheet references to pull the data that you're looking for, from the source sheets with all the rows on them.

You can do that with a COLLECT function. For example, let's say you want to collect the data from rows that are in Stage="Not Started". Assuming you have a column called Stage in your detailed data sheets, applied to each row that's submitted, you can use a formula like this in the "document collection sheet" to collect the first column of data. I'll call it "Detail Column".

=JOIN(COLLECT({Detail Column}, {Stage}, "Not Started"), ";")

The {} references are cross sheet reference that you insert while typing out the formula. While typing, click the "Reference cell in another sheet" link in the formula helper popup box, then select the column from the detailed sheet that you're trying to reference (select the whole column by clicking the column header).

Once you have your row setup in your "document collection sheet" with the data that you want from the detailed sheets, you can setup an automation for document generation that triggers on changes to the rows. Although many automations won't trigger from a cross-sheet reference, I think document builder will. If not, you can schedule the document creation instead of triggering it.

ShaRobinson

Hi Brian, that was a well thought-out response, and explained very clearly! Thank you for taking the time to respond.

I thought of this same solution earlier today, and started early steps of developing it — but then (dreading the worst) I stopped to query if there are any limitations on quantity of cross sheet references per sheet. And unfortunately there is a limit of 100 unique references.

My document sheet will have over 250 columns, and each one would need a unique reference. I'm so grateful I stopped to check before creating 100 references!

Best in class response from you, though. Thanks!

Sharon

Brian_Richardson

250 columns! Good lord :-)

Ok, so I would do it slightly differently then and leverage the Hierarchy that Smartsheet provides. Hierarchy sets Smartsheet apart from other spreadsheet tools, it's very useful!

In your sheet with the data, instead of writing formulas with fixed ranges like you've done, instead indent the rows with the data underneath your documentation row. Then on the documentation row use

=JOIN(COLLECT(CHILDREN([Detail Column 1]@row, CHILDREN([Stage]@row), "Not Started"), ";")

ShaRobinson

Hi Brian,

I was able to work with that! I had to tweak the process and run that JOIN/COLLECT on a second sheet instead after automation copies the rows to it (as it wasn't playing nicely with a different formula I need on the original sheet to manage some input from forms). And I actually like that it will generate the document on the 2nd sheet, so that's good : )

The only bummer is that I have to manually indent the child rows to create the hierarchy, but as soon as I do, there's my row with all the data! (I tried tricking the sheet by pre-setting the JOIN/COLLECT data row as a parent to a fake helper row below it, but the added rows outdented to same as the JOIN/COLLECT data row.)

Any thoughts on how to get the "JOIN(COLLECT…" to maintain the number formatting from the cell it's collecting the data from? The cell data is properly formatted ($, comma, .00 as applicable), but $653,459 comes in like 653459. DEFINITELY not a Day 1 deal-breaker though!

Thanks for your help!

Brian_Richardson

When you use JOIN it creates text, whether the source data is numbers or text. If you want a numeric value out of the results then wrap it in a VALUE function and then you should be able to format it to your needs. (As long as the JOIN isn't lumping a couple of numbers together like 12345;123123. If that's the case VALUE won't work, you'd need to separate those values by picking up the first set with a LEFT or MID.