I have written a macro that should meet your initial requirements of adding, changing or removing items from the "invoice" worksheet, as data is added or changed on the "costs" worksheet.
I suggest that you start with a new workbook and try this out before trying to change addresses to match your exact layout.
Open a new workbook.
Rename two worksheets to "invoice" and "costs" (don't use the double quotes when entering the worksheet names)
Save the new workbook as an Excel Macro Enabled Workbook "InvoiceMacro.xlsm"
On the invoice worksheet enter "Your order" in cell B5
Select cells B6 to B26 and format the cells borders with a grid
On the costs worksheet enter Description, Quantity, Width, Height, Option, Order text, Product list & Option list in cells A1 to H1
In cells G2 to G6 enter 5 product names (invites, rsvp etc.)
In cells H2 and H3 enter 1 and 2
Select cells A2 to A22, and add data validation to them, selecting the List option and using the range =$G$2:$G$6
Select cells E2 to E22, and add data validation to them, selecting the List option and using the range =$H$2:$H$3
In cell F2 enter this formula:
=IF(B2>0,B2&" "&A2&". Size "&C2&"mm by "&D2&"mm. "&E2&" sided.","")
Drag the formula to extend it down to cell F22
Size column B on the invoice worksheet and column F on the costs worksheet to accommodate the Order text description
Now right-click the "costs" worksheet name tab and select 'View Code'
paste this into the large Visual Basic code window:
Private Sub Worksheet_Change(ByVal Target As Range)
'test if changed cell is in our item description area
'change description range area if necessary
If Not Intersect(Target, Range("A2:E22")) Is Nothing Then
Dim rngCell As Range
Dim intInvRow As Integer
'disable events so that changes made by this macro
'won't re-trigger it
Application.EnableEvents = False
'set invoice text area first row
intInvRow = 6
're-do invoice list from scratch with changed data
'delete all existing invoice data
'change invoice item list range as required
'get new data from costs worksheet
'change number of rows used in cost worksheet if necessary
For Each rngCell In Worksheets("costs").Range("B2:B22")
If rngCell.Value > 0 Then
'valid quantity - so copy text in Col. F to next invoice row
Worksheets("invoice").Range("B" & CStr(intInvRow)).Value _
= rngCell.Offset(0, 4).Text
'increment invoice row
intInvRow = intInvRow + 1
Application.EnableEvents = True
Application.EnableEvents = True
Save the workbook
This macro responds to any changes in data on the "costs" worksheet
If the change is in the data input range A2 to E22, then the rest of the code runs.
Rather than finding and deleting or updating data on the "invoice" worksheet, it just clears the data from the range B6 to B26, and creates a new list based on the changes you have made, selecting only from rows on the "costs" worksheet that have a quantity of greater than zero.
Once this is working you can start adjusting ranges for input data and invoice text to match your detailed requirements.
As you suggested this could become a template with each new workbook named for the customer - or preferrably a Customer Order number, so that further orders from the same customer will not over-write a previous order.
It would then be possible to add a button to the invoice that prints the invoice and saves summary data to a common Summary workbook.
If you would like, I can send you the sample workbook I have used.
To do this send me a private message with an email address (do not post your email address in a response).