?
Solved

Custom Function for OR Function to Test for more than 50 items?

Posted on 2012-08-15
5
Medium Priority
?
375 Views
Last Modified: 2012-08-15
I am trying to create a formula to check a column of entries to make sure the entries are either a 2 digit state or the word "foreign".  I am thinking about using the OR function and tagging the items as error if they don't meet any of the items.  The problem is that these entries are copied and pasted from elsewhere so validation is out.  The entries could also have extra spaces which is not so evident, but would cause a problem because this data is used in pivot tables.

Can I create a custom function for this or use some kind of list of data in the OR Function?

Thanks, Eric
0
Comment
Question by:ekaplan323
5 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38297984
=IF(ISNUMBER(H12)=TRUE,"NUMBER",IF(H12="Foreign","Word",""))

Prior to checking it, I would suggest doing a find and replace on that column for spacings.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38298010
"two digit State"?

Do you have a list of these? Can you supply some examples

regards, barry
0
 

Author Comment

by:ekaplan323
ID: 38298121
Here is a sample of the data.  The states could be any of the postal codes for the 50 states and other than those it coud say foreign.

Eric
Sample-Validation.xlsx
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38298196
If you can create a lookup table of valid entries, you could use Conditional Formatting in the column you're checking.  You'll need to select the column and set the Conditional Formatting after you receive a final version, as pasting will overwrite the format.

Set this formula in Conditional Formatting rule.  Assumes list of valid entries on Sheet2 in Column A.

=IF(OR(A4="",ISERROR(MATCH(A4,Sheet2!$A:$A,0))<>TRUE),FALSE,TRUE)
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 38298216
I listed the State abbreviations in M2:M52 and put "Foreign" in M53, then I used this formula in F8 copied down

=IF(COUNTIF(M$2:M$53,C8),"OK","Error")

I believe the errors in rows 15 and 17, for example, are caused by some trailing spaces

See attached

regards, barry
States.xls
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question