Create error message when VLOOKUP formula returns specific value

[prodacct]

Hi everyone! I am hoping someone can help me with this problem. Is there a way to have an error message pop up when a cell with a vlookup formula returns a specific value.

For example:

I have a workbook with two tabs. Tab one is my master list of names with names in column one and column two is if there are missing documents in their file. In tab two (my daily working tab) I have a pull down list in which column one is a pull down from the list of names from the master tab. Column two is a vlookup formula to lookup the "missing documents" column from the master list after the name has been selected from the pull down list.

Is there a way that once the name is selected from the drop down list and the vlookup formula has returned a specific value to have an error message pop up to let the inputter know there are documents missing.

I have attached my test document as an example as well. I hope someone out there can help me out!! THANK YOU!!

[teylyn]

Hello,

if you want to pop up an error message, you would need VBA. Are you comfortable with that?

As a formula alternative, you can use conditional formatting to turn the row red or something when Vlookup returns Yes.

For the conditional formatting approach, select A1 to B6, then click on the Home ribbon Conditional Formatting > New Rule > use a formula ... > enter this formula

=$B1="Yes"

then select a format like a red fill color. Now the table row will light up in red if the value in column B of that row is "Yes".

A pop up would appear once. The conditional format will stay and be a visible warning to the user until the value of the cell in column B changes.

See attached.

cheers, teylyn

[prodacct]

Thanks for this! Yes I was actually looking for someone to help me out with a VBA solution as opposed to conditional formatting for this specific instance. Hopefully someone can assist with the VBA solution. Thanks!

[Trebor76]

Hi prodacct,

Try this event macro on the tab called Worksheet:

Please Login or Register  to view this content.

Regards,

Robert

[prodacct]

Hi Robert! This code worked perfectly! Is there a way to not have a "Runtime Error 13 mismatch" show up if I delete the info in column 1?

Thanks so much!!

[Trebor76]

See how this goes:

Please Login or Register  to view this content.

Regards,

Robert

[Trebor76]

See how this goes:

Please Login or Register  to view this content.

Regards,

Robert

[prodacct]

It is perfect! Thanks so much Robert!!!

SOLVED!

[Trebor76]

It is perfect! Thanks so much Robert!!!

SOLVED!

That's great. If you could mark the thread as solved it would be appreciated.

Regards,

Robert