VBA Sorting due to Formula

[Wardylewis]

Good Afternoon,

I have created a sheet which pulls information from another sheet to generate an easy to read plan.


However due to the formulas in the cell is

=IFERROR(LOOKUP(2,1/('Data Input'!$A$1:$A$2500='Live Check'!$A661),'Data Input'!$R$1:$R$2500),"No Records Held")

When I do a standard sort it works for a split second and then changes and gives me a different result every time I sort.

I have tried to take a look online to edit the Macro I have done but am really struggling to get my head around it.

Sub Sort()'
' Sort Macro
'


'
Dim rangeToSort As Range


    ActiveWorkbook.Worksheets("Live Check").ListObjects("Table1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Live Check").ListObjects("Table1").Sort.SortFields. _
        Add Key:=Range("Table1[[#All],[Next Test Date]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Live Check").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Any help would be greatly appreciated.

 

[Wardylewis]

Doing some additional tests it seems to be only affected when formatted in a table.

Apart from removing this formatting is there any other way around it?

 

[Fluff]

What happens if you remove the part in blue
=IFERROR(LOOKUP(2,1/('Data Input'!$A$1:$A$2500='Live Check'!$A661),'Data Input'!$R$1:$R$2500),"No Records Held")