Dear All,

We are using Excel 365, and we created an excel which imports data from SQL Server using Power Query, and on each sheet the cells are updated with formulas.

What we have observed and we dont know how to fix is that when we change datasource to a Database which has no data inside the formulas on sheets remains OK, but when we change the datasource and there are data inside the power query table then the formulas on other sheets cells change:

Example

For example the Power-Query sheet after the refresh gets 20 rows of data then the formula on Sheet '1' changes from
B8:=IFERROR(INDEX(PowerQuery!$A$4:PowerQuery!$A$100, XMATCH($D$2 & $D$3,PowerQuery!$B$4 : PowerQuery!$B$100 & PowerQuery!$C$4 : PowerQuery!$C$100,1)),"")

To:

B8=IFERROR(INDEX(PowerQuery!$A$24:PowerQuery!$A$100, XMATCH($D$2 & $D$3,PowerQuery!$B$24 : PowerQuery!$B$100 & PowerQuery!$C$24 : PowerQuery!$C$100,1)),"")

What it does is that changes the formula of the cell from $B$4 to $B$24 and as a result we dont get the accurate data on cells.

Is there a way to correct this issue, because the database will start from 0 and as rows are added everyday we are quite unsure if the excel will change the formulas by itself.


Thanks in advance.

Best Regards,

RR