asked on

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

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

Shanan212

=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.

barry houdini

"two digit State"?

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

regards, barry

ekaplan323

ASKER

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

mark_harris231

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)

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial