Hey guys,

I need assistance writing two formulas into two different columns in a table in Excel. Now, I know some people warn against doing that. However, the worksheet gets sent to all team members in my company to keep track of cost/profit/projects coming in and out. Consequently, building out a VLookUp or something else doesn't work. Below is the code, everything that I have tried is marked as comments. The table that needs the added formula is 16 columns wide, and there is a second table in the worksheet that draws information from the first table-it is like a shorthand version.

I need one formula to add up three columns-- phone cost, data cost, and commission cost--which are in columns 12,13, and 14. The formula should go into column 15 (Total Cost)
The second formula subtracts the Total Cost (column 15) from the Invoice Amount (column 4), and places it in Profit (column 16).

Sub InvoiceTracker()

Dim lrow As Integer
Dim InvoiceTracker As ListObject
Dim i As Integer
Dim InvoiceEmail As ListObject

'Cost/Profit

'Range("O4").Select
'ActiveCell.FormulaR1C1 = "=SUM(InvoiceTracker[@[Phone Costs]:[Commission Costs]])"
'Range("P4").Select
'ActiveCell.FormulaR1C1 = "=[@[Invoice Amount]]-[@[Total Cost]]"

'Sheets("Invoice Tracker").ListObjects("InvoiceTracker").ListColumns("Total Cost"). = "=SUM(ListObjects(""InvoiceTracker"").ListColumns(""Phone Cost"":""Commission Cost""))"

'Sheets("Invoice Tracker").ListObjects("InvoiceTracker").ListColumns("Total Cost").Calculation = "=SUM(InvoiceTracker[[Phone Cost]:[Commission Cost]])"

'Sheets("Invoice Tracker").ListObjects("InvoiceTracker").ListColumns("Total Cost").CalculationSum = "="

'Sheets("Invoice Tracker").ListObjects("InvoiceTracker").ListColumns("Total Cost").DataBodyRange.FormulaR1C1 = "=SUM("
'Last attempt and I stopped part way through...'

'Converting Invoice Tracker to Table




'Setting up email table

Range("T6").Value = "Client"
Range("U6").Value = "Invoice"
Range("V6").Value = "Amount"
Range("W6").Value = "Cost"
Range("X6").Value = "Profit"

Sheets("Invoice Tracker").Range("V:V, W:W, X:X").NumberFormat = "$#,##0.00"

Sheets("Invoice Tracker").Range("T6:X6").Select

With selection.Font
        .Bold = True
        .Size = 12
        .Name = "Cambria"
End With

'Copying data from input table to shorthand table'

Sheets("Invoice Tracker").Range("InvoiceTracker[Client]").Copy
Range("T7").PasteSpecial Paste:=xlPasteValues

Sheets("Invoice Tracker").Range("InvoiceTracker[Invoice Number]").Copy
Range("U7").PasteSpecial Paste:=xlPasteValues

Sheets("Invoice Tracker").Range("InvoiceTracker[Invoice Amount]").Copy
Range("V7").PasteSpecial Paste:=xlPasteValues

Sheets("Invoice Tracker").Range("InvoiceTracker[Total Cost]").Copy
Range("W7").PasteSpecial Paste:=xlPasteValues

Sheets("Invoice Tracker").Range("InvoiceTracker[Profit]").Copy
Range("X7").PasteSpecial Paste:=xlPasteValues

'Test Conversion'

'i = 6

'Do Until Cells(i, 20).Value = ""
 '   i = i + 1
'Loop

'Debug.Print i

'HeaderRows = 6

'Sheets("Invoice Tracker").ListObjects.Add(xlSrcRange, Range("T6:X "" & i-1 & """), , xlYes).Name = "InvoiceEmail"

'Converting Range to Table'

Sheets("Invoice Tracker").ListObjects.Add(xlSrcRange, Range("T6").CurrentRegion, , xlYes).Name = "InvoiceEmail"
Sheets("Invoice Tracker").ListObjects("InvoiceEmail").TableStyle = "TableStyleMedium3"
Sheets("Invoice Tracker").ListObjects("InvoiceEmail").ShowTotals = True
Sheets("Invoice Tracker").ListObjects("InvoiceEmail").ListColumns("Amount").TotalsCalculation = xlTotalsCalculationSum
Sheets("Invoice Tracker").ListObjects("InvoiceEmail").ListColumns("Cost").TotalsCalculation = xlTotalsCalculationSum

'With Sheets("Invoice Tracker").ListObjects("InvoiceEmail")
'    .Sort.SortFields

'Add after so it doesn't become part of the table

Range("T5").Value = "Invoice Tracker for"
Range("U5").Value = "=TODAY() -1"

Sheets("Invoice Tracker").Range("InvoiceEmail").Select

With selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .ColumnWidth = 13.5
    .WrapText = True
End With





End Sub
The other thought I had was to write out everything I need everyday, and then convert the range to a table using the macro. But I don't know if that is necessarily easier or better.

Any guidance on the matter would be appreciated, I have been playing around for a day or two to no avail.

Cheers,
Rachelle