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

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

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