This is either an "existing connections" issue or a spreadsheet issue

[kaywai]

Hi All,
I just came across the "Existing Connections" in the Data Tab and have been experimenting its usefulness to my work. I saw potential that it would save me a lot of time and so my journey of experimentation began.

I have stock data which I placed in a workbook called Data Workbook. I then initiate a connection from another workbook to pull data (Cells A1 to F13 if you're using the sample worksheet) from Data Workbook. Once the data is pulled to the second workbook, which already has formulas in them, calculations are automatically done. When I then add new data to the Data Workbook, and then do a refresh from the second workbook, the new data Cells A14 to F32) shows up nicely but nothing is being calculated even though there formulas are in the cells that uses that data. Unfortunately I can't replicate this when I place the data into a sample worksheet. I've also attached a screenshot where the initial data is from A502 to F514 and the updated data is from A515 to F533.Attachment 718470

The Calculations Options is still ticked at Automatic. I also tried Calculate Now and Calculate Sheet on the Formulas Tab. Neither work.

I then went back to the old method of copying and pasting the data from the Data Workbook to the second workbook. Everything worked!

I then recopied all the formulas into the second workbook. And everything including the new data is being used in the calculations.

Where does the problem lie and how do I fix it? I have quite a few worksheets relying on this to work the way it should.

Could someone please help?

Regards
Kay Wai

[kaywai]

I sort of figured what happened now.
When I refresh and new data comes in, they come in as "rows" in that the formulas references are shifted down by the number of new rows of of data. Take for example Column H row 510 and row 514. Note the shift in row reference from 514 to 533. That took into account new data from 20210118 to 20210211.

Does anyone know how I can fix that?