Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
incrementing account numbers | Excel Discussion (Misc queries) | |||
Macro for Finding account numbers | Excel Discussion (Misc queries) | |||
Generate new account numbers? | New Users to Excel | |||
Sorting Account Numbers | Excel Worksheet Functions | |||
How do I setup a list box that has several account numbers so use. | Excel Worksheet Functions |