Validation of Account Numbers
 

I ma reparing a budget entry sheet with numerous general ledger account
numbers in column A. I want to validate that column A has accurate general
ledger accounts numbers. How can I compare each cell in column A to a
general ledger account code listing,let's say in colmn M? If the account code
in Column A is not in column M, then it would either stop or provide an error
listing where the comparison failed. Is this a "If-then" statement? If so,
how do I code it?

Validation of Account Numbers
 

Try something like this:

With
A2:A30 containing account numbers (some may be invalid)
and
M2:M100 contains a list of valid account numbers

This formula flags Col_A accts as either "OK" or "Invalid Acct"
B2: =IF(SUM(COUNTIF(A2,$M$2:$M$100&"")),"OK","Invalid Acct")

Copy that formula down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"trunzop" wrote:

I ma reparing a budget entry sheet with numerous general ledger account
numbers in column A. I want to validate that column A has accurate general
ledger accounts numbers. How can I compare each cell in column A to a
general ledger account code listing,let's say in colmn M? If the account code
in Column A is not in column M, then it would either stop or provide an error
listing where the comparison failed. Is this a "If-then" statement? If so,
how do I code it?

Validation of Account Numbers
 

You can use Conditional formatting to hi-light any errors. Select A1 and
pull-down:

Format Conditional Formatting... Formula Is
=COUNTIF(M:M,A1)<1
and pick a distinctive background color

copy A1 and paste/special/formats down the column
--
Gary's Student
gsnu200709


"trunzop" wrote:

I ma reparing a budget entry sheet with numerous general ledger account
numbers in column A. I want to validate that column A has accurate general
ledger accounts numbers. How can I compare each cell in column A to a
general ledger account code listing,let's say in colmn M? If the account code
in Column A is not in column M, then it would either stop or provide an error
listing where the comparison failed. Is this a "If-then" statement? If so,
how do I code it?

Validation of Account Numbers
 

Thank you Ron, this should work. I appreciate the quick response!

Paul

"Ron Coderre" wrote:

Try something like this:

With
A2:A30 containing account numbers (some may be invalid)
and
M2:M100 contains a list of valid account numbers

This formula flags Col_A accts as either "OK" or "Invalid Acct"
B2: =IF(SUM(COUNTIF(A2,$M$2:$M$100&"")),"OK","Invalid Acct")

Copy that formula down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"trunzop" wrote:

I ma reparing a budget entry sheet with numerous general ledger account
numbers in column A. I want to validate that column A has accurate general
ledger accounts numbers. How can I compare each cell in column A to a
general ledger account code listing,let's say in colmn M? If the account code
in Column A is not in column M, then it would either stop or provide an error
listing where the comparison failed. Is this a "If-then" statement? If so,
how do I code it?