Drop down list, transfer cell to table.

Bafuller83

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I made a Excel sheet that has a drop down list of different forms of payments and to the left of that is the amount that was paid.

I then have a second table (direct payment) which have the headers of each type of payments. And the amounts below.

I have been inputting the amounts manually to the second table. How can I transfer the payment amount automatically based on the drop down payment type to the designated payment type in the second table.

I attached a screenshot of the excel sheet from my phone.

example: if payment type from the drop down list is “Zelle”, transfer the payment amount the the Zelle column of the Direct Payment table.

thanks for your help, I know very little lol
 

Attachments

  • 897D8CCA-4322-4B9D-B222-9862EAEF2591.png
    897D8CCA-4322-4B9D-B222-9862EAEF2591.png
    196.6 KB · Views: 9

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column C.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Dim LastRow As Long
    Select Case Target.Value
        Case "Zelle"
            LastRow = Range("I3", Range("I" & Rows.Count).End(xlUp)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
            If Target.Offset(, -1) <> 0 Then
                Target.Offset(, -1).Copy
                Cells(LastRow, 9).Insert
            End If
        Case "Venmo"
            LastRow = Range("H3", Range("H" & Rows.Count).End(xlUp)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
            If Target.Offset(, -1) <> 0 Then
                Target.Offset(, -1).Copy
                Cells(LastRow, 8).Insert
            End If
    End Select
    Application.CutCopyMode = False
End Sub
 

Bafuller83

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column C.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Dim LastRow As Long
    Select Case Target.Value
        Case "Zelle"
            LastRow = Range("I3", Range("I" & Rows.Count).End(xlUp)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
            If Target.Offset(, -1) <> 0 Then
                Target.Offset(, -1).Copy
                Cells(LastRow, 9).Insert
            End If
        Case "Venmo"
            LastRow = Range("H3", Range("H" & Rows.Count).End(xlUp)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
            If Target.Offset(, -1) <> 0 Then
                Target.Offset(, -1).Copy
                Cells(LastRow, 8).Insert
            End If
    End Select
    Application.CutCopyMode = False
End Sub
Thanks, I’ll try that when I can. How in the world did you come up with that? That’s amazing!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Once you get some knowledge of VBA, the coding is fairly common and straightforward. Let me know how it works out.
 

Bafuller83

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

ok, so the code works, but if I change the drop down from venmo to zelle the input for the previous will continue to be present for it. Is there a way around that? So that it will delete or undo if changes to different types of payments?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Adding a "cash" option is not a problem. The problem with undoing changes is that you may have more than one occurrence of a particular payment in column H or column I. The macro will have no way of knowing which one to undo. For example, if you have three zelle values in either column and then you change the payment type to venmo, the macro will have no way of knowing which of the three zelle values to change. Does that make sense? I could modify the code to change the very last value entered, in other words, the value at the bottom of the list, but not any of the previous values.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,315
Messages
5,623,950
Members
416,001
Latest member
teabag

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top