Getting error if linked cell row deleted in different sheet of large data.

[babaso_tawase]

I have two excel sheets. Excel sheet 1 having data about 10000 row, in sheet 2 I am doing calculation using different formula. I have given right to delete insert row in sheet1 as it requires. In sheet 2 I have used below formula

a	b	c
1	=INDIRECT("'Sheet1'!D"&a1)	10	=b1*c1
2	=INDIRECT("'Sheet1'!D"&a2)	20	=b2*c2
3	=INDIRECT("'Sheet1'!D"&a3)	30	=b3*c3

above formula used because I can fill down for 10000 row. It work perfectly means after deletion or addition row in sheet 1 no impact on sheet2 data. But problem if data changed in sheet 1 then data in b column in sheet 2 not updating on saving or pressing F9. Is any solution for this?

 

[Jeffrey Mahoney]

Please try this

Book2
	A	B	C	D
1	1	100	10	1000
2	2	101	20	2020
3	3	102	30	3060
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=INDIRECT("'Sheet1'!D"&A1)
D1:D3	D1	=OFFSET(D1,0,-2)*OFFSET(D1,0,-1)

 

[babaso_tawase]

Thanks. its working fine.

 

[Peter_SSs]

above formula used because I can fill down for 10000 row

With volatile functions (INDIRECT and OFFSET) in 2 columns x 10000 rows doesn't the sheet become pretty sluggish?

I'm not sure I understand exactly what you were doing the caused the problem, but would this non-volatile option in Sheet2 work for you? If it does then sheet performance should improve. If this doesn't work for you then can you detail (examples) just what your are doing so that other non-volatile options might be considered?

babaso_tawase.xlsm
	B	C	D
1	100	10	1000
2	101	20	2020
3	102	30	3060
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=INDEX(Sheet1!D:D,ROW())
D1:D3	D1	=B1*C1