Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a worksheet (WORKSHEET 1) with over 1,000 static serial numbers. On a another worksheet (WORKSHEET 2) I have approximately 100 different cells where serial numbers are entered. I want to create a formula within the cells on WORKSHEET 2 that will check against the 1,000 serial numbers in WORKSHEET 1. If any of the static serial numbers from WORKSHEET 1 are entered into any of the cells in WORKSHEET 2 then I would like the cell to highlight, display a message, etc... to alert the person entering the data to stop and flag the serial number and move on.
Solved by D. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 11/09/2018 - 04:30
Are you there?
Excelchat Expert 11/09/2018 - 04:30
Hello.
User 11/09/2018 - 04:30
Hi
Excelchat Expert 11/09/2018 - 04:31
Sorry for the delay, my browser froze.
User 11/09/2018 - 04:31
No worries
Excelchat Expert 11/09/2018 - 04:31
Anyway, so you want to check if a number in sheet 2 already exist in Sheet1, right?
User 11/09/2018 - 04:32
Yes. We have a guy that will enter in serial numbers in sheet 2. We have to check against sheet 1 to ensure that we do not enter any of the serial numbers from sheet 1 into any of the particular fields in sheet 2.
User 11/09/2018 - 04:32
If he enters in a serial number from sheet 1 we want there to be some sort of alert for him to remove the number and contact the vendor for a new serial number.
Excelchat Expert 11/09/2018 - 04:33
I see. I can help you with creating this data validation. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert 11/09/2018 - 04:33
Are the numbers in Sheet1 all in one column?
User 11/09/2018 - 04:33
OK, no worries.
User 11/09/2018 - 04:33
Yes that is correct. They begin in A2 and go to A1001
Excelchat Expert 11/09/2018 - 04:34
Will that always be up to row 1001?
Excelchat Expert 11/09/2018 - 04:34
Or do you see the possibility that it will expand?
User 11/09/2018 - 04:34
Yes, these numbers will never change.
Excelchat Expert 11/09/2018 - 04:35
Okay, how about in Sheet2, which cell will need to be validated?
Excelchat Expert 11/09/2018 - 04:35
I meant, which cell will users usually input a number in?
User 11/09/2018 - 04:35
There are multiple. Starting with Cell H2- H32 as well I2 - I32, J2-J32 and K2-K32.
Excelchat Expert 11/09/2018 - 04:36
Okay, we just need 1 and then we can simply copy and paste that cell to all the cells you require the data validation.
User 11/09/2018 - 04:36
Yes, OK, sounds good.
Excelchat Expert 11/09/2018 - 04:36
I'll be working on this locally so please give me a few minutes to prepare the steps.
User 11/09/2018 - 04:36
Sure thing.
Excelchat Expert 11/09/2018 - 04:37
While I'm working on it, please check on this Excel file.
[Uploaded an Excel file]
Excelchat Expert 11/09/2018 - 04:38
The yellow section in Sheet2 is where you can type some numbers.
Excelchat Expert 11/09/2018 - 04:38
If you type a number that is already in Sheet1 then it will not allow you to enter that number.
Excelchat Expert 11/09/2018 - 04:38
Let me know if this is what you need.
User 11/09/2018 - 04:38
OK, opening it now.
User 11/09/2018 - 04:39
Yes, that is what I am looking for.
Excelchat Expert 11/09/2018 - 04:39
Okay, let me prepare the detailed steps.
User 11/09/2018 - 04:40
OK
Excelchat Expert 11/09/2018 - 04:42
Do you see the sheet to the right?
User 11/09/2018 - 04:42
Yes.
Excelchat Expert 11/09/2018 - 04:42
Okay, I wrote the detailed steps in there.
Excelchat Expert 11/09/2018 - 04:43
Copy the steps so you can reference it anytime. Feel free to try the steps while I'm with you.
User 11/09/2018 - 04:43
OK, and then I will just copy that formula into the other corresponding cells that need to check for these numbers?
Excelchat Expert 11/09/2018 - 04:43
Just don't forget to extend the session when prompted.
Excelchat Expert 11/09/2018 - 04:43
Not really the formula but the cell itself.
User 11/09/2018 - 04:43
OK, let me give it a whirl. OK, how do I extend the session?
User 11/09/2018 - 04:44
I see atimer with 6 mins left/
Excelchat Expert 11/09/2018 - 04:44
The cell will have the settings and if you copy the cell and paste it in another cell, it will inherit the data validation.
User 11/09/2018 - 04:44
Ah, OK cool.
Excelchat Expert 11/09/2018 - 04:44
At the 3 minute mark, you'll be prompted.
User 11/09/2018 - 04:44
OK good deal. Ok, let me try...
Excelchat Expert 11/09/2018 - 04:44
If you miss the prompt, it may be possible that you won't be able to extend any further so please pay attention.
User 11/09/2018 - 04:49
I think I did it correctly with the timer. I am still working on the formula in the cell and testing.
Excelchat Expert 11/09/2018 - 04:49
Sure take your time.
Excelchat Expert 11/09/2018 - 04:52
Here's a screenshot that may help if you encounter any problems doing it.
[Uploaded an Excel file]
User 11/09/2018 - 04:52
OK, I think I suck at this or Im not doing it correctly.
Excelchat Expert 11/09/2018 - 04:53
Try to follow the steps and also use the screenshot above as a reference.
User 11/09/2018 - 04:54
I have attached my spreadsheet so you can see what i doing here.
[Uploaded an Excel file]
User 11/09/2018 - 04:54
Wait, wrong one.
User 11/09/2018 - 04:55
Here we go. Should see Sheet 4 with all the serial numbers and Sheet 1.
[Uploaded an Excel file]
User 11/09/2018 - 04:56
Cell H2 on Sheet 1 is the first cell that I need to validate this info in.
Excelchat Expert 11/09/2018 - 04:57
Okay, I can see what's wrong. Good news is that you've done it correctly.
Excelchat Expert 11/09/2018 - 04:57
The problem is that your serial number column is formatted as text.
Excelchat Expert 11/09/2018 - 04:58
This means that if you type a number in Sheet1, even if it matches the serial in Sheet4, they won't match.
Excelchat Expert 11/09/2018 - 04:58
So we will need to reformat your column A to numbers. Would that be okay?
User 11/09/2018 - 04:59
Oh yes, that is perfectly fine/
Excelchat Expert 11/09/2018 - 05:00
Before we do that, try this first:
Excelchat Expert 11/09/2018 - 05:00
=NOT(ISNUMBER(MATCH(H2&"",Sheet1!$A$2:$A$1001,0)))
Excelchat Expert 11/09/2018 - 05:00
Try that formula instead, see if that works for you.
Excelchat Expert 11/09/2018 - 05:00
That way, we won't have to reformat your Sheet4.
User 11/09/2018 - 05:02
BAM! That is AWESOME. Yes, that worked. It allowed for me to insert a regular serial number but not the ones on the sheet.
Excelchat Expert 11/09/2018 - 05:02
By the way, I also noticed that you used Warning instead of a complete STOP. Choosing warning will give the user an option to go ahead and input the number even if the user inserts an invalid one.
User 11/09/2018 - 05:03
Oh OK, I will change that as well.
Excelchat Expert 11/09/2018 - 05:03
That's under Error Alert > Style
User 11/09/2018 - 05:03
OK, perfect.
Excelchat Expert 11/09/2018 - 05:03
I think we got it?
User 11/09/2018 - 05:03
WOW! Thank you so, so very much for this help.
Excelchat Expert 11/09/2018 - 05:03
Glad to have helped!
Excelchat Expert 11/09/2018 - 05:04
Would there be anything else that I can help you with regards to the original question?
User 11/09/2018 - 05:04
Yes, thanks again. Have a great wonderful Day.
Excelchat Expert 11/09/2018 - 05:04
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 11/09/2018 - 05:04
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 11/09/2018 - 05:04
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User 11/09/2018 - 05:04
Absolutely 5 Stars!
User 11/09/2018 - 05:05
Oh, and I can just do the data validation method the same the for the other cells in Sheet 1 right?
Excelchat Expert 11/09/2018 - 05:05
No need, just copy Cell H2 and paste over the other cells.
User 11/09/2018 - 05:06
Oh, right on.
Excelchat Expert 11/09/2018 - 05:06
Select cell H2, CTRL+C then highlight where you want to apply the same data validation then CTRL+V
Excelchat Expert 11/09/2018 - 05:07
Session will expire in 2 minutes. Please do not forget to click the End session button so you can rate me.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.