Macro to add value from input sheet into another sheet

Dpat

Please find attached my version of your file- in that I've modified your Module2 and assigned that to the Submit button on the Add Invoice sheet (from where it should be run).

The key changes are in bold in the listing below. Firstly I've defined a new variable q_ref (for use later) then moved your working code to the start of the macro.

I note that your macro does NOT disobey the database guidance provided by @Variatus under the Discussion (since a new line is added each time to the Invoiced sheet). You'll see your "working" code is now in a With / End With statement (purely to reduce the amount of text) but the dots are important (so .Cells refers to the sheet stated in the With but Range does not).

Once that sheet has been done, I add a new With (for the sales funnel, a snapshot in which you're just looking for what's coming, balance left to bill etc.). Instead of your Find, I've looped through used cells in column B and (if a cell matches  q_ref), that row is the one to modify for values. If it's not found, a message alerts the user (and the # around q_ref will show up and unwanted spaces around it).

I then modify the Total Invoice and Balance Due values to allow for the new invoice value. Also, if that line isn't already showing Won, the macro changes it to that status.

Here's the macro code:

Sub add_invoice1()

'Define sheets as variables
invoice_output = "invoiced"
funnel_output = "sales funnel"
q_ref = Range("quote_ref").Value

With Sheets(invoice_output)
    'Adds new row
    next_row = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    'Adds data from form into new row
    .Cells(next_row, 1).Value = Range("invoice_date").Value
    .Cells(next_row, 2).Value = Range("quote_ref").Value
    .Cells(next_row, 3).Value = Range("install_date").Value
    .Cells(next_row, 4).Value = Range("project_name").Value
    .Cells(next_row, 5).Value = Range("invoice_amount").Value
    .Cells(next_row, 6).Value = Range("p.o.").Value
End With

' Modify funnel, if possible
With Sheets(funnel_output)

    'Find the quote ref given in the form, in funnel tab
    For r = 7 To .Cells(Rows.Count, 2).End(xlUp).Row ' loop from row 7 to last used row in B
        If .Cells(r, 2).Value = q_ref Then
         row_quote = r
        End If
    Next r

    ' say if quote_ref not found
    If row_quote = "" Then
        MsgBox "Updated " & invoice_output & " but #" & q_ref & "# not found in " & funnel_output
        Exit Sub ' skip revision to funnel
    End If


    'Get the cell with the existing quote value (column #7)
    existing_invoice_value = .Cells(row_quote, 7).Value

    'Get the new invoiced value from the form ## different
    new_invoice_value = Range("invoice_amount").Value

    'Replace the invoice values on the funnel tab, with the new value added just above
    If .Cells(row_quote, 5) <> "Won" Then .Cells(row_quote, 5) = "Won" 'correct cell (since invoice raised)
    .Cells(row_quote, 7).Value = existing_invoice_value + new_invoice_value
    .Cells(row_quote, 8).Value = .Cells(row_quote, 8) - new_invoice_value ' adjust balance

End With

MsgBox "Updated " & invoice_output & " sheet for " & q_ref & " and revised " & funnel_output

End Sub

Hope this works for you.