How to stop losing formula when refreshing data

[Godders199]

Hello, i have a spreadsheet that refreshes from a database, however everytime i press refresh the $A$1:$AZ$1 is lost

=LEFT(ADDRESS(1,MATCH(A2,checksmart!$A$1:$AZ$1,0)*1,4),2)

After refresh

=LEFT(ADDRESS(1,MATCH(A2,checksmart!#REF!,0)*1,4),2)

I have checked another spreaadsheet that uses the same feed and formula and that works fine, i cannot find any differences. is there any way i can prevent the formula changing when the data is refreshed?

 

[jmacleary]

Hi there:

This may be to do with the way the table is defined. Check the external data properties of your table - set the 'if the number of rows changes on refresh' to 'overwrite existing cells with new data, clear unused cells'.

 

[Godders199]

thanks, this appears to be all the code there is relating to the refresh from the source.
Sheets("Info").Select
Calculate

Qlink = Sheets("Info").Range("A56").Value

Sheets("checkSmart").Select

Range("A1:AZ50000").Select
Selection.Delete Shift:=xlUp

ActiveWorkbook.XmlImport URL:=Qlink, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")


do i need to change something?

 

[jmacleary]

Ah. It will be the 'Selection.Delete Shift:=xlUp'. This removes all the rows, and so the formula cannot reference them. Replace it with 'Selection.ClearContents' and see what happens.

 

[Godders199]

thanks alot, that works.

 

[jmacleary]

You're welcome. Would you be kind enough to click on the thanks button for my answer>