Changing Formula from absolute to Relative

I would have to post the whole work book.

Basically it is a summary sheet which is the first sheet of the workbook. The summary sheet lists employee names and various totals against their name in other columns and column U is a key column this also holds the employee name.

On the summary sheet in the example above the column is called 'core sales' as it is used to display the value of sales with a sale type of core for this reason 'core' is appended to the key in the formula.

the formula to fill this column looks up the data from a second workheet which displays all the detals of sales, this is the Invoiced sheet. The column we are summing up is column J which is the value of each sale. The invoiced sheet also has a key column which is made up of the name of the employee making the sale and the sale type this is Column AB. This allows us to match the key on the summary and sum up each persons sales from the Invoiced sheet where the sale type is of type for example 'Core'

Interesting, but although most of the helpers in this forum can read minds, they lack the ability to read non-posted workbooks.

Ok I have uploaded a sample which should demonstrate whats going on.

Basically it currently works perfectly, but if a user then does a sort on the data then then because it is all absolute the formula start displaying the wrong data which is why i need to change it.

'Summary sheet
Const colSummarySalesPerson = 1
Const colSummarySalesTeam = 2
Const colSummaryDelivered = 3
Const colSummaryDeliveredCost = 4
Const colSummaryDeliveredMargin = 5
Const colSummaryDeliveredMarginPerc = 6
Const colSummaryInvoicedCore = 7
Const colSummaryInvoicedCoreSL = 8
Const colSummaryInvoiced3Party = 9
Const colSummaryInvoiced3PartySL = 10
Const colSummaryInvoicedProduct = 11
Const colSummaryInvoicedOther = 12
Const colSummaryInvoicedTotal = 13
Const colSummaryOutstandingCore = 14
Const colSummaryOutstandingCoreSL = 15
Const colSummaryOutstanding3Party = 16
Const colSummaryOutstanding3PartySL = 17
Const colSummaryOutstandingProduct = 18
Const colSummaryOutstandingOther = 19
Const colSummaryOutstandingTotal = 20
Const colSummaryTotalCore = 21
Const colSummaryTotalCoreSL = 22
Const colSummaryTotal3Party = 23
Const colSummaryTotal3PartySL = 24
Const colSummaryTotalProduct = 25
Const colSummaryTotalOther = 26
Const colSummaryTotalTotal = 27

Const colSummaryKey1 = 28

'Bookings
Const colBookingSalesPerson = 1
Const colBookingSalesTeam = 2
Const colBookingEvent = 3
Const colBookingEventType = 4
Const colBookingPractice = 5
Const colBookingVendor = 6
'Const colBookingSubject = 7
Const colBookingLocation = 7
Const colBookingStartDate = 8
Const colBookingStartPeriod = 9
Const colBookingDuration = 10
Const colBookingNumber = 11
Const colBookingSplit = 12
Const colBookingCustomer = 13
Const colBookingCostCalc = 14
Const colBookingRRP = 15
Const colBookingRevenue = 16
Const colBookingCost = 17
Const colBookingMargin = 18
Const colBookingMarginPerc = 19
Const colBookingAddDate = 20
Const colBookingStatus = 21
Const colBookingConfirmDate = 22

Const colBookingKey1 = 23

'Product
Const colProductSalesPerson = 1
Const colProductSalesTeam = 2
Const colProductCode = 3
Const colProductDescription = 4
Const colProductRRP = 5
Const colProductCostCalc = 6
Const colProductCost = 7
Const colProductMargin = 8
Const colProductMarginPerc = 9
Const colProductNumber = 10
Const colProductSplit = 12
Const colProductRevenue = 11
Const colProductCustomer = 13
Const colProductAddDate = 14
Const colProductStatus = 15
Const colProductConfirmDate = 16
Const colProductAddedBy = 17
Const colProductInvoiced = 18
Const colProductKey1 = 19

'Invoices/Credits

Const colInvoicedBookingSalesPerson = 1
Const colInvoicedBookingSalesTeam = 2
Const colInvoicedCourseProduct = 3
Const colInvoicedEventNumber = 4
Const colInvoicedLocation = 5
Const colInvoicedStartDate = 6
Const colInvoicedStartPeriod = 7
Const colInvoicedEventType = 8
Const colInvoicedSplitPercentage = 9
Const colInvoicedSplitTotal = 10
Const colInvoicedRevenueType = 11
Const colInvoicedBookNumber = 12
Const colInvoicedCustomerName = 13
Const colInvoicedAltRef = 14

Const colInvoicedInvoiceNumber = 15
Const colInvoicedWeekComencingDate = 16
Const colInvoicedInvoiceDate = 17
Const colInvoicedBookConfDate = 18
Const colInvoicedRevenueClassification = 19
Const colInvoicedLicenseNumber = 20
Const colInvoicedKey1 = 21

Const colNotInvoicedBookAddDate = 15
Const colNotInvoicedBookStatus = 16
Const colNotInvoicedBookConfDate = 17
Const colNotInvoicedWeekComencingDate = 18
Const colNotInvoicedExpectedInvoicePeriod = 19
Const colNotInvoicedRevenueClassification = 20
Const colNotInvoicedLicenseNumber = 21
Const colNotInvoicedKey1 = 22

=SUMIF( Invoiced!AA17:AA65009, AH26 & "/Core", Invoiced!P17:P65009)

is the same as
=SUMIF( Invoiced!$U$8:$U$65000, $AB$17 & "/Core", Invoiced!$J$8:$J$65000)

with an offset(9,6)

Im not quite sure what you mean.

When I use the code to change the formula to relative

the workbook then renders like the workbook here instead of like the one above.

I changed
Application.ConvertFormula cl.Formula, xlR1C1, xlA1, xlRelative
to
cl.Formula = Application.ConvertFormula(cl.Formula, xlA1, xlA1, xlRelative)
and now the majority of values come out ok
Except the ones in columns c,d,e on the summary sheet which now just say #VALUE

Thats the ones

 'Delivered
        TempArray(Currenti, colSummaryDelivered) = _
            "=SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Company""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/RoomHire""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Onsite""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(Public!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Public""" & _
            ", Public!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(3Party!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/3Party""" & _
            ", 3Party!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ")"

       'DeliveredCost
        TempArray(Currenti, colSummaryDeliveredCost) = _
            "=SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Company""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/RoomHire""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Onsite""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(Public!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Public""" & _
            ", Public!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(3Party!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/3Party""" & _
            ", 3Party!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ")"

        'DeliveredMargin
        TempArray(Currenti, colSummaryDeliveredMargin) = _
            "=SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Company""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/RoomHire""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Onsite""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(Public!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Public""" & _
            ", Public!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(3Party!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/3Party""" & _
            ", 3Party!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ")"

        'Delivered Margin %
         TempArray(Currenti, colSummaryDeliveredMarginPerc) = _
            "=IF(R" & intRow & "C" & colSummaryDelivered & "<>0,R" & intRow & "C" & colSummaryDeliveredMargin & "/" & "R" & intRow & "C" & colSummaryDelivered & ",0)"

Did you overlook this one

Wouldn't it be easier to form the addresses first, and then reuse them? Also, you can look at the addresses ...

    Dim sAdr1       As String
    Dim sAdr2       As String
    Dim sAdr3       As String
    Dim sAdr4       As String
    Dim sAdr5       As String

    sAdr1 = Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address
    sAdr2 = Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address
    sAdr3 = Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address
    sAdr4 = Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address
    sAdr5 = Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address

    'Delivered
    TempArray(Currenti, colSummaryDelivered) = _
    "= SUMIF(Private!" & sAdr1 & ", " & sAdr2 & """/Company"", Private!" & sAdr3 & ") " & _
    "+ SUMIF(Private!" & sAdr1 & ", " & sAdr2 & """/RoomHire"", Private!" & sAdr3 & ") " & _
    "+ SUMIF(Private!" & sAdr1 & ", " & sAdr2 & """/Onsite"", Private!" & sAdr3 & ") " & _
    "+ SUMIF(Public!" & sAdr1 & ", " & sAdr2 & """/Public"", Public!" & sAdr3 & ") " & _
    "+ SUMIF(3Party!" & sAdr1 & ", " & sAdr2 & """/3Party"", 3Party!" & sAdr3 & ")"

    'DeliveredCost
    TempArray(Currenti, colSummaryDeliveredCost) = _
    "= SUMIF(Private!" & sAdr1 & ", " & sAdr2 & " & ""/Company""" & ", Private!" & sAdr4 & ") " & _
    "+ SUMIF(Private!" & sAdr1 & ", " & sAdr2 & " & ""/RoomHire""" & ", Private!" & sAdr4 & ") " & _
    "+ SUMIF(Private!" & sAdr1 & ", " & sAdr2 & " & ""/Onsite""" & ", Private!" & sAdr4 & ") " & _
    "+ SUMIF(Public!" & sAdr1 & ", " & sAdr2 & " & ""/Public""" & ", Public!" & sAdr4 & ") " & _
    "+ SUMIF(3Party!" & sAdr1 & ", " & sAdr2 & " & ""/3Party""" & ", 3Party!" & sAdr4 & ")"

    'DeliveredMargin
    TempArray(Currenti, colSummaryDeliveredMargin) = _
    "= SUMIF(Private!" & sAdr1 & ", " & sAdr2 & " & ""/Company""" & ", Private!" & sAdr5 & ") " & _
    "+ SUMIF(Private!" & sAdr1 & ", " & sAdr2 & " & ""/RoomHire""" & ", Private!" & sAdr5 & ") " & _
    "+ SUMIF(Private!" & sAdr1 & ", " & sAdr2 & " & ""/Onsite""" & ", Private!" & sAdr5 & ") " & _
    "+ SUMIF(Public!" & sAdr1 & ", " & sAdr2 & " & ""/Public""" & ", Public!" & sAdr5 & ") " & _
    "+ SUMIF(3Party!" & sAdr1 & ", " & sAdr2 & " & ""/3Party""" & ", 3Party!" & sAdr5 & ")"

    'Delivered Margin %
    TempArray(Currenti, colSummaryDeliveredMarginPerc) = _
    "=IF(R" & intRow & "C" & colSummaryDelivered & "<>0,R" & intRow & "C" & colSummaryDeliveredMargin & "/" & "R" & intRow & "C" & colSummaryDelivered & ",0)"