Data via web query in Excel for Mac

Hi.

I'm experimenting with importing external data from a website into an excel sheet.

So far, I have successfully managed to get the data into a worksheet by using the 'run web query' functionality and subsequently using a .iqy file with the relevant url.

I have built quite a few formulas in order to process the imported data (for example formulas to change dates from the import that were imported as text ("Nov 5, 2023") and changing them values (45235)).

This brings me to my challenge:

When running 'refresh all' function, the added data from my query are inserted below the existing data in the import area. However, it seems that new data are added as 'inserted cells', which means that my existing formulas suddenly have a 'gap'.

Illustrative example:

In A18 there is a formula referring to D18 where A18 is outside the imported area and D18 is within the imported data area. Imagine for this example that the imported data currently ends in row 18. However, to manage added data later, I have the formula from A18 copied down to cells below. I.e. A19 refers to D19. 

After updating the data, there is now imported data in D19. However, the D19-reference in my original formula in A19 has been updated and now refers to D20, which means I am missing data from D19.  

I hope the challenge is clear.