Automating Copying/Updating NON-BLANK Cells From One Sheet to Another Using a Unique ID Cell

MLamph

Hey Everyone!

I'm trying to set up a form of automation that's quite a bit more advanced than anything I've done in Smartsheets before and can't seem to find a solution. I'm admittedly pretty new to the advanced functions of Smartsheets.

In this scenario, I am using a form to evaluate skills of field engineers. The evaluator enters several pieces of information about the engineer being evaluated, including their email address.

Because a form cannot UPDATE a line, but instead can only ADD a line, I would like to create some automation that triggers each time a new line is added. The function should find the corresponding email address in the destination sheet, and update ONLY the cells for which a response has been entered.

As an added challenge, I would prefer if some cells (unique ID and other classifying information) on the destination sheet are locked from being updateable by the automated process.

Any guidance here would be greatly appreciated!

Genevieve P.

Hey @MLamph

First thing to note is that if you're looking to update a row (versus create a new row), you could use Update Requests instead of a Form Link. You could automate the request being sent out (e.g. once a week) if you want to write overtop of data on the same row. Here's more information: Update Requests

If you would prefer to keep it as a Form sending information into an Intake Sheet, what I would do here is set up formulas in your second sheet to take a look at the information added and display that same content.

An INDEX(MATCH formula will index a particular column to look for a value, and if it finds a row with a matching ID (e.g. the matching email address in your intake sheet), it will display the corresponding information:

=INDEX({Column to return}, MATCH([Email Address]@row, {Email Address Intake Sheet}, 0))

See: Formula combinations for cross sheet references

This will bring back a blank cell for any of the columns you're referencing that are blank.

Let me know if you need help setting this up or would like to see screen capture examples!

Cheers,

Genevieve

MLamph

https://community.smartsheet.com/discussion/comment/398045#Comment_398045

Thanks Genevieve.

Unfortunately, Update Requests is not an ideal solution. In this situation, Sr. Engineers and other designated experts will be evaluating engineers in real time, and will need to use the form as a sort of checklist, and then submit that information at the end of each evaluation. This dataset will have hundreds of columns, each representing a specific task or skill that a field engineer should eventually complete or master. The form is set up to expand by category so as not to overwhelm the evaluator. Manually editing or updating the sheet would be difficult to manage.

The problem with an INDEX formula is that the same engineer may be evaluated multiple times over a long enough period of time. We want the most recent value for each cell. Not all skills will be evaluated with each update, so there will be blank cells that should be ignored when updating. I only want to update cells associated with the Unique ID that have a value and only for the last line entered.

I believe the formula or automation must happen from the intake solution so that the addition of a new line can be the trigger.

Genevieve P.

Hi @MLamph

If there are multiple matching rows, we can actually use INDEX to bring back the most recent content (based on the location of the sheet) that is not blank (so it keeps previous content showing if the new row is blank for that area).

What I would do here is ensure that new additions are put to the TOP of the sheet, so they'll be the first match found if there are multiple rows with content in that same column.

Then instead MATCH we use COLLECT, as the Collect function allows us to filter on more than 1 criteria.

=IFERROR(INDEX(COLLECT({Column to Return}, {Column to Return}, <>"", {Email Column}, [Email Column]@row), 1), "")

Here's an example in the same sheet, with the blue columns pretending they're in another sheet:

Cheers,

Genevieve