Solved

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

Posted on 2012-08-15
368 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
Question by:ekaplan323
5 Comments

LVL 13

Expert Comment

=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

"two digit State"?

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

regards, barry
0

Author Comment

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

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

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

## Join & Write a Comment Already a member? Login.

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!