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.
