Custom Valdiation Help

mattie

New Member
Joined
Mar 26, 2010
Messages
20
I am having some issues with custom validation, in cel N6 i have some custom valdiation set up to see if that value appears in a list on a seperate worksheet and if it doesnt pop up a error box, the code i currently have is:

=INDIRECT("Users!A2:A17115")

But when i try and test it the error box keeps coming up. I think i need to add soemthing to it but am not sure what.

cany anyone help?

Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
Greetings,

Try using a Name. In the Refers To: =Users!$A$2:$A$17115

Name it MyRef (or whatever)

In the cells you want DV, in the Allow box, choose List. In the formula box, "=MyRef"

Hope that helps,

Mark
 

mattie

New Member
Joined
Mar 26, 2010
Messages
20
Thanks Mark, the issue i am havng now is that when i put incorrect enteries in the error box isnt coming up
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
Hmmm... I just tried it, so I suspect I didn't communicate some part.

On the Error Alert tab of the dialog for defining the DV, do you have Style selected as Stop?
 

mattie

New Member
Joined
Mar 26, 2010
Messages
20
Yeah, i have also removed the in cell drop down as i want the users to input their id and then the validation check that it is contained on the list and if it is continue and if not throw up the message
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
I cleared the In Cell Dropdown checkbox and it still get stopped cold.

My bet is that you have blank cells in your list.
 

Forum statistics

Threads
1,175,990
Messages
5,900,754
Members
434,848
Latest member
kubersood321

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