Excel 2013 Convert Positive to Negative if another cell contains specific text

I have an Excel (2013) file that lists transactions for our AP team. This report is run on a regular basis by accounts payable.

Column D indicates whether the transaction is "Invoice" or "Credit Memo".

Column I is the transaction dollar amount.

Is there a way that I can convert the transaction dollar amount (Column I) from a positive to a negative number if Column D states "Credit Memo"? It would really only need to be a one time run when the file is opened as the report is generated on a regular basis.
AP-Transaction-Report.xlsx
mpsgrpAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
You can use VBA to perform this task....

Sub ConvertCreditMemoAmount()
    Dim cell As Range
    For Each cell In Range([I2], [I2].End(xlDown))
        If cell.Offset(0, -5) = "Credit Memo" Then
            cell.Value = -cell.Value
        End If
    Next    
End Sub

Open in new window

0
 
Tj aCommented:
You can create a new column that has the original Document Amount values but have this column in place where its not visible e.g column AC. Replace the old column with this formula.

=IF(D2 = "Credit Memo", AC2* -1, AC2)

See the attached file for an example
AP-Transaction-Report.xlsx
0
 
mpsgrpAuthor Commented:
Worked absolutely perfectly. Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.