Exclude a range of values from a source range of values

Luke High

Hey all,

I've got Sheet 1 - List 1 that is an intake sheet with a range of opportunities (each identified by a unique opportunity number with a set number of digits) exported from our CRM program. This sheet is automatically updated whenever a seller puts a new opportunity into the CRM. I am not allowed to make changes to or edit this sheet.

I've got Sheet 2 - List 2 that is an intake sheet with a range of opportunities that have been manually entered into our system via a form. I am also not allowed to make changes or edits to this sheet.

I've created a new Sheet 3, which is my target sheet and is blank.

What I'm trying to do is create a list of opportunity numbers in Sheet 3 from List 1 that excludes any opportunity numbers that have already been manually entered into the system via List 2. Essentially List 1 minus List 2.

The closest I've come to solving this is using =IF(CONTAINS({List 1 Cell}, {List 2 Range}), "Nope", {List 1 Cell})

This formula returns the original opportunity number if not found in List 2, and returns "Nope" if it IS found in List 2. It's working, however the issue is that this formula only works for a single cell. I'm unable to enter a range into the "search for" field of the CONTAINS function. And I'm unable to add an @row modifier to List 1 because it is an external sheet reference.

I've tried looking into Data Mesh, INDEX/MATCH, VLOOKUP, etc. but it seems like in all cases I need to be referencing some kind of data that's already been entered into Sheet 3, which there is none.

I could also solve the problem if I were able to copy and paste the original data from List 1 into sheet 3 automatically, but it doesn't seem like that functionality is in Smartsheets either.

KPH

When you say you are not allowed to make changes to or edit sheet 1, does this extend to not being able to ask for one extra column to be added? If you could get an auto number column added to sheet 1 you would have a column you could use to reference in an INDEX/COLLECT. Is that a possibility for you?

Luke High

The source sheet (Sheet 1) is a national level intake that's used across the country, so people are understandably skiddish about making changes to it. I'm going to be having some discussions about doing exactly what you suggested, but was hoping there was another way to do it in case I get a "No".

Luke High

Ah. Turns out the reason I can't add columns to the Sheet 1 data is because we're frequently already hitting the cell limit for a sheet because we've got too much data.