Gesyca_is_joy
Board Regular
- Joined
- Apr 24, 2014
- Messages
- 90
- Office Version
- 365
- Platform
- Windows
So I have an Excel workbook with two tabs; What i am trying to do is write a conditional formatting formula that will highlight the account number red if I enter an account number that is not funded.
The formula I am thinking of has 3 parts, which I have broken out below, but I can't figure out how to get the formula to work, am I over thinking this?
Full Formula Currently: =AND($A2<>"",$M2="")=INDEX(INDIRECT('Chart of Accounts'!$C$4:$C$1002),MATCH($H2,INDIRECT('Chart of Accounts'!$A$4:$A$1002),0))=<>
Tab 1: Invoices:
The formula I am thinking of has 3 parts, which I have broken out below, but I can't figure out how to get the formula to work, am I over thinking this?
Full Formula Currently: =AND($A2<>"",$M2="")=INDEX(INDIRECT('Chart of Accounts'!$C$4:$C$1002),MATCH($H2,INDIRECT('Chart of Accounts'!$A$4:$A$1002),0))=<>
- =AND($A2<>"",$M2="")
- Checks to make sure the entry has an invoice number and not a check number (this is because funding changes over time and I don't want to see the formatting on records that have already been paid.
- =INDEX(INDIRECT('Chart of Accounts'!$C$4:$C$1002),MATCH($H2,INDIRECT('Chart of Accounts'!$A$4:$A$1002),0))
- Performs and INDEX MATCH on the Account number to match it with Account number on Tab 2 and check if that account has Funding
- =<>
- If the INDEX MATCH formula above returns the cell in column C with any value then it is funded, if that column is blank then the account has no funding.
Tab 1: Invoices:
- Column A: Invoice Number
- Column M: Check Number
- Column H: Account Number
- Column A: Account Number
- Column C: Funded (will have a value or will be blank)