New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Custom function in Data Validation

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Custom function in Data Validation

Postby hellofriend » Wed Nov 04, 2015 6:11 pm

Hi All,

The other question is I have a column that will only accept whole numbers. However I would like to use the 'Custom' function' so that the user is also allowed to enter NA if required. Please advise as to how I can do this. Thank you in advance.
  • 0

Last edited by pecoflyer on Thu Nov 05, 2015 2:27 am, edited 1 time in total.
Reason: Removed first question
hellofriend
Rookie
 
Posts: 20
Joined: Oct 17, 2013
Reputation: 0
Excel Version: 2010

Re: Custom function in Data Validation

Postby pecoflyer » Thu Nov 05, 2015 2:29 am

Hi
to get better and faster answers, it's better to start a new thread per different question.
Posting two identical threads with two questions might not be want you want and is confusing to the members.

I adapted these threads for you

Cheers
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Custom function in Data Validation

Postby edwardsantiago » Wed Mar 30, 2016 3:30 am

First, name the cells in the first column:
Select cells A2:A3
Click in the Name Box, to the left of the Formula Bar, and type a one-word name for the range, e.g. EmpIDs
Press the Enter key, to complete the naming.
Add the Data Validation:
Select cells A2:A3
On the Ribbon's Data tab, click Data Validation
On the Settings tab, from the Allow drop down, choose: Custom
NOTE: If there could be blank cells in the column, remove the check mark from "Ignore Blank Cells", or duplicates will be allowed
In the formula box, you'll use the COUNTIF function to check value being entered in cell A2. It will be compared to all the entries in the EmpID column. To prevent duplicates, the count must be 1 or 0:
=COUNTIF(EmpIDs,A2) <= 1
  • 0

edwardsantiago
Rookie
 
Posts: 2
Joined: Mar 30, 2016
Reputation: 0
Excel Version: excel2011


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 54 guests