Dynamic update of formula in excel

[BMD44]

Hi,

I have a scenario where in I need to update sum of the rows dynamically.


R17=A#AC_1510201000
R18=A#AC_1510203000
R19=A#AC_1510127000
R20=A#AC_1510127000
R21=A#AC_1510777000
R22=SCalc(Row(19)+Row(20)+Row(21)+Row(23))


In the above example, Row 22 is sum of R17 till R21. I reworked on the rows and the row numbers have changed. Now, I am looking for a way to have R22 dynamically updated to SCalc(Row(17)+Row(18)+Row(19)+Row(20)) instead of SCalc(Row(19)+Row(20)+Row(21)+Row(23)).
If a new row is added/deleted,then the formula should be updated dynamically. Can any one please suggest an approach.

Thanks in advance.

 

[James006]

Hi,

Can you explain what is SCalc ... ???

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-formulas-and-functions/1291061-dynamic-update-of-formula-in-excel.html#post5200368

 

[offthelip]

by the sound of what you are trying to do , you are fighting against the excellent behaviour of excel where it updates all the equations when you insert of delete rows.
The only way can think of to get round this is to use the indirect function. However this a volatile function which means it is recalculated on every calculation so this could make your workbook slow
An example in using assuming trying to sume A1 to A10 with:

=SUM(A1:A11)

change this to:

=SUM(INDIRECT("A1:A10"))