Thread: Results Cells equals Partial Text

Ok!! I believe I am close. I have tried everyone's formulas/functions in a previous thread and it seems to be close when inserted to a particular cell, but when I try to create what will be input and used on a normal basis, nothing pulls appropriately to K, L. When I plug in the formula in one cell, it might work there, but when I copy down, it does not.

Here is exactly what I am trying to accomplish. Employee will enter the description of an expense in column "A". In column "B" the date and in "E" the respective amount. Easy enough. Now what I am trying to do is allow the employee to use the dropdowns in "H" to correctly classify the respective expense. In column "J", should just match the total in "E".

The tricky part is now getting the 4 digit portion of the Account Code's that are used in "H" only to be reflected in column "J", BUT only once, if used, and consolidated for the number of entries or itemized expenses.

EXAMPLE: if I enter "Gas" in "A" and 02/08/12 for the date in "B" and $53.00 for "E" for the amount. I use the dropdown and select, "6502-Auto & Truck Expense-Fuel, Gas & Propane-Cost of Goods Sold". Column "J" should also reflect $53.00.

I need column "K" to reflect "6502" (only) and $53.00 to appear in Column "L". Now if I enter Office Supplies in "A", 02/08/12 in "B" and $32.00 in "E". I use dropdown and select "7310-Office Equipment/Supplies-Consumable Supplies-SG&A Expense" - Column "J" should read $32.00. Column "K" should show "7310" and "L" should show $32.00.

If I enter another subsequent "Gas" or 6502 or "Office Supplies" or 7310 expense in other entries (for example another "Gas" entry for $51.00), Column "K" should still only have one '6502' cell and "L" should now read "104.00".

If there also anyway for columns E, J & L to be blank without a prefilled "$" or a "0" until an entry is made, would be an added bonus!!!!!!!!

This is the best way I can explain it perhaps. I have attached another 'cleared field' spreadsheet. Can anyone just input these formulas that clearly seem to work for you (and me) until I try to copy and paste down. I can't tell you how much I would appreciate it!!!! Please let me know if there are any questions. I thought I was somewhat saavy enough to do this, but have proven my shortcomings in detail!!!!

Demo Excel Sheet Test.xlsx

Originally Posted by mrice

This can be done by event driven macro.

Try pasting the following into the Sheet1 tab in the VBA editor (Alt F11)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim N As Long
Dim Prefix As String
Dim TargetRow As Long

For Each Cell In Target
    If Cell.Column = 8 Then
        Application.EnableEvents = False
        Range(Cells(2, 11), Cells(Rows.Count, 12)).ClearContents
        For N = 8 To Cells(Rows.Count, 8).End(xlUp).Row
            Prefix = Left(Cells(N, 8), 4)
            If WorksheetFunction.CountIf(Columns(11), Prefix) = 0 Then
                Cells(Rows.Count, 11).End(xlUp).Offset(1, 0) = Prefix
            End If
            TargetRow = Columns(11).Find(Prefix, , xlValues, xlWhole).Row
            Cells(TargetRow, 12) = Cells(TargetRow, 12) + Cells(N, 5)
        Next N
        Application.EnableEvents = True
        
    End If
Next Cell
End Sub

Martin, I highlighted your "code", not sure how or where to paste?!?