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.

ASIA MASTER WORKBOOK test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
510	20210111	1.71	1.71	1.68	1.69	8877100		1.71	1.68			3	3	0
511	20210112	1.7	1.71	1.69	1.71	10920200		1.71	1.69			4	4	0
512	20210113	1.71	1.72	1.69	1.71	8512200		1.72	1.69			5	5	0
513	20210114	1.7	1.72	1.69	1.71	9212600		1.72	1.69			0	0	0
514	20210115	1.71	1.75	1.71	1.72	16845400		1.71	1.56			0	0	-1
515	20210118	1.72	1.72	1.68	1.69	10003300		1.57	1.57			0	0	-2
516	20210119	1.69	1.71	1.68	1.69	9324900		0	0			0	0	-3
517	20210120	1.69	1.7	1.68	1.69	8303000		0	0			0	0	-4
518	20210121	1.69	1.69	1.67	1.68	13394800		0	0			0	0	-5
519	20210122	1.67	1.68	1.65	1.65	15534600		0	0			0	0	-6
520	20210125	1.65	1.66	1.63	1.63	8694500		0	0			0	0	0
521	20210126	1.63	1.63	1.61	1.62	12512700		0	0			0	0	0
522	20210127	1.61	1.64	1.61	1.63	7412700		0	0			0	0	0
523	20210128	1.6	1.61	1.59	1.59	12624700		0	0			0	0	0
524	20210129	1.61	1.62	1.58	1.58	12500300		0	0			0	0	0
525	20210201	1.56	1.59	1.56	1.58	9902900		0	0			0	0	0
526	20210202	1.6	1.61	1.57	1.59	11765900		0	0			0	0	0
527	20210203	1.6	1.61	1.58	1.58	7094400		0	0			0	0	0
528	20210204	1.59	1.6	1.56	1.59	13994400		0	0			0	0	0
529	20210205	1.59	1.59	1.57	1.58	7061400		0	0			0	0	0
530	20210208	1.58	1.6	1.58	1.59	4497100		0	0			0	0	0
531	20210209	1.61	1.63	1.58	1.59	13950000		0	0			0	0	0
532	20210210	1.6	1.6	1.57	1.58	9691800		0	0			0	0	0
533	20210211	1.58	1.58	1.56	1.57	6772900		0	0			0	0	0
cd

Cell Formulas
Range	Formula
H510:H513	H510	=MAXA(E509,C510)
I510:I513	I510	=MINA(E509,D510)
J510:J533	J510	=IF(L510=-9,MAX(H501:H510),"")
K510:K533	K510	=IF(L510=9,MIN(I501:I510),"")
L510:L513	L510	=IF(AND(E509>E505, E510<E506),-1, IF(AND(E509<E505,E510>E506),1, IF(AND(E510<E506, L509<0),L509-1, IF(AND(E510>E506, L509>0),L509+1,0))))
M510:N533	M510	=IF(AND(H509>H505,H510<H506),-1, IF(AND(H509<H505,H510>H506),1, IF(AND(H510<H506, M509<0),M509-1, IF(AND(H510>H506, M509>0),M509+1,0))))
H514	H514	=MAXA(E513,C533)
I514	I514	=MINA(E513,D533)
L514	L514	=IF(AND(E513>E509, E533<E510),-1, IF(AND(E513<E509,E533>E510),1, IF(AND(E533<E510, L513<0),L513-1, IF(AND(E533>E510, L513>0),L513+1,0))))
H515:H533	H515	=MAXA(E533,C534)
I515:I533	I515	=MINA(E533,D534)
L515:L517	L515	=IF(AND(E533>E510, E534<E511),-1, IF(AND(E533<E510,E534>E511),1, IF(AND(E534<E511, L514<0),L514-1, IF(AND(E534>E511, L514>0),L514+1,0))))
L518	L518	=IF(AND(E536>E513, E537<E533),-1, IF(AND(E536<E513,E537>E533),1, IF(AND(E537<E533, L517<0),L517-1, IF(AND(E537>E533, L517>0),L517+1,0))))
L519:L533	L519	=IF(AND(E537>E533, E538<E534),-1, IF(AND(E537<E533,E538>E534),1, IF(AND(E538<E534, L518<0),L518-1, IF(AND(E538>E534, L518>0),L518+1,0))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L:N	Cell Value	=0	text	NO
J:J	Cell	does not contain a blank value	text	NO
K:K	Cell	does not contain a blank value	text	NO
L:N	Cell Value	=1	text	NO
L:N	Cell Value	=-1	text	NO
L:N	Cell Value	<=-9	text	NO
L:N	Cell Value	>=9	text	NO

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 A510 to F514) 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 A515 to F533) shows up nicely but nothing is being calculated even though there formulas are in the cells that uses that data.

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 to from 514 to 533.

Does anyone know how I can fix that?