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.
