If cell equals a range then display a warning message

Wawinator

Board Regular
Joined
Jan 16, 2014
Messages
51
Hello,

I am an absolute noob at excel so please bear with me.

I need to create a rule in excel which will display a warning or attention message if anyone inputs specific codes from a list that I have. It is ok to input other codes but not the one in the list that I have.

for example:

ABClist
10
20

<TBODY>
</TBODY>


In column "A", if I put 10 or 20 I want a warning message to pop. but if anything else is put nothing will happen

Also, is it possible to include another conditions in column "A" that also look at another list and display a different message?

Thank you very much for your help.

Wawinator
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
Hi Wawinator,

Select cell A1, press Alt + D + L
Settings tab,
Allow:- Custom
formula:- =NOT(ISNUMBER(MATCH(A1,$D:$D,0)))

Now you cannot enter any value from column D in cell A1. If this is fine, copy the validation rule to other cells of your choice. :)


Regards,
DILIPandey
 

Wawinator

Board Regular
Joined
Jan 16, 2014
Messages
51
Hello DILIPandy,

Thank you very much for your swift reply.

I tried the formula but it didn't work for me. I typed a number from the list that I have in the specific cell but nothing happened.

Also, I need this to work in the entire "A" column. For example, If i type any of the numbers I have on my list in any cell of column "A", then a warning messge will pop up.


Any other advise?


Thank you for sharing your knowledge with me

regards,

Wawinator
 

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
I tested it before replying to you.
Can you list down the steps you followed? Thanks.

Regards,
DILIPandey
 

Wawinator

Board Regular
Joined
Jan 16, 2014
Messages
51

ADVERTISEMENT

Thank you again DILIPandy,

I did it again and it worked :), sorry about that. But i need it to work for the entire "A" column and not just A1.

any suggestions?

thank you again for your swift reply and help.

regards,
Wawinator
 

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
Okay..

For entire column A, you need to copy the validation in entire column from cell A1. Ok.. u can follow below steps:-
copy cell A1
Select column A
Go to paste -> paste special
choose validation in left section
Ok


Regards,
DILIPandey
 

Wawinator

Board Regular
Joined
Jan 16, 2014
Messages
51

ADVERTISEMENT

Hello DILIPandy...you are a genius!

Thank you so much my friend :)

regards,

Allan
 

Wawinator

Board Regular
Joined
Jan 16, 2014
Messages
51
Hi DILIPandy,

Quick question, is is possibly to put more then one validation? i.e another list and another message on the same cell??

thanks again for sharing your knowledge.

regards,

Allan
 

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
You are welcome Allan,

Off-course we can check more than one list for a particular value - would be great if you share the scenario. Thanks.


Regards,
DILIPandey
 

Wawinator

Board Regular
Joined
Jan 16, 2014
Messages
51
Thank you again,

The scenario is as follows. There is more then one list, I have 3 lists that needs the similar validation to the previous one you helped me with but from a different list. For example, I have 3 lists that have different codes, I need a different message to pop up depending on what is similar to the list. I want this all to occur in the entire Column "A".

for example, if any thing from list 1 is typed in comlum "A" "hello appears", if anything from list 2 is typed in column "A" "thank you " appears etc.

Thank you again my friend for sharing your knowledge with me.

regards,

Wawinator
 

Watch MrExcel Video

Forum statistics

Threads
1,127,827
Messages
5,627,124
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top