Formula to Copy cells if no match?

Hello @vdemattei

So, you will use a lookup function that looks at one sheet and if there is an error, to look at the other sheet. Since you haven't provided any column names or functions, I'm going to try to be accurate in the functions, but let me know if you don't understand.

=IF(ISERROR(INDEX/MATCH from sheet 1)=TRUE, Lookup function for sheet 2, Lookup function for sheet 1)

If the result is still an error, you can use a checkbox helper column that points to the lookup function.

=IFERROR(lookup@row,1)

In this case, 1 just means TRUE. Depending on how frequently you refresh the data, you could set up the automation to run daily to copy rows over. I recommend it so that automations run consistently. Sometimes a trigger can be missed, especially using lookup functions.

Another way would be to use the submission form. When a new row is added, copy it over to the other sheet and you won't have to worry about watching both sheets.

@vdemattei

I generally write it like that because they are used together. It is not a division formula.

I re-read your thread a little bit and changed my mind. Unless you're going to use a third sheet to analyze the data between the two, then you will need the function I recommended.

On a third sheet he set up will be similar to:

=IF(ISERROR(INDEX({Workflow-Name},MATCH(Name@row,{Screening-Name},0)))=TRUE, INDEX({Screening-Name},MATCH(Name@row,{Workflow-Name},0)), INDEX({Workflow-Name},MATCH(Name@row,{Screening-Name},0)))

The 0 means you're looking for an exact match.

You will need to set up the references in the sheet before this function will work. Can't copy and paste it unfortunately.

I recommend calling the ranges something else than Name so you have an idea of which sheet the function is actually looking at. I usually enter the reference name as the "sheet - column". Ex: Opportunities - OP Value

If you have multiple criteria, instead of just 1 lookup value, the INDEX/COLLECT functions would be the way to go, although it uses more data than the INDEX/MATCH functions so if you have A LOT of cross-references, or a large sheet containing the source data, than you may want to reorganize the data within the sheets.

Smartsheet recently upgraded the amount of references a sheet can contain, so it's much harder to cause it to load slowly.

The sheet names wouldn't matter in the function directly, but it will matter when you're setting up the reference within the function. It's a popup window with all your workspaces. Easiest way to find a sheet would be to use the search bar if you have a lot going on in your account.

Another option, would be to use a checkbox column (this is our helper) in the Screening Sheet. The function would be a combo of the two functions I suggested above. Since we already know it's contained within the second sheet.

=IFERROR(INDEX({Workflow-Name},MATCH(Name@row,{Screening-Name},0)),1)

The first option would be good if you have more analytics involved, such as a count of how many people are contained in one sheet and not the other. Having a separate sheet specific to the analytics is easier to manage and report aggregate data, like a one stop shop.

The second option would be better if you only need to use the sheet and all the data you need to use or report is straight forward with minimal aggregate data.

It all depends on how you want to collect, review, and present the data.

@vdemattei

Use this function in the source sheet and reference the range in the target sheet.

=COUNTIF({Range of Values in Target Sheet}, [Value in Source Sheet]@row)

This will count how many of that value is contained within the target sheet.

The automation will have a condition that this helper column needs to equal 0. (Value is not contained at all in the target sheet)

I run the automation on a schedule and I think this is the easiest way, so if any cells in the helper column have a 0, then it will be copied over to another sheet. If it's not 0, then it's already on the sheet and the condition will prevent the automation from copying duplicate rows.

@vdemattei

Change the range in the Match function to [Name]:[Name].

Then use the countif function to verify whether the information is on the other sheet. You can still use a checkbox.

@vdemattei

So it worked after switching around the references?

The IFERROR function will check the box if the value isn't found within the source. Is this working?

I've also had to use ID #s. Especially with customers.

I set up an autonumbering system so that an ID is assigned when a person is entered into the list. Then I don't have to worry about people who somehow made it to the list multiple times. But yes, this is an issue for me as well. The "quick fix" was to assign IDs, but then everyone needs to know their ID and that throws another twist in the mix.

@vdemattei Oh cool, you're getting into more complicated features. You'll be writing code with API calls in no time.