Replied 8 September 2017, 1:58 pm EST

Hi Deepak, thanks for responding.

I have read that thread but I'm not sure I understand how it helps.

Are you suggesting that:

a) In Step 1 of my task, the user enters a custom formula, say, "=ROWSUM(2:4)";

b) Steps 3 & 4 of my task are done by statements in ROWSUM Evaluate;

c) Step 5 of my task is accomplished by getting ROWSUM to return "=ROWSUM(2:4)" as a text string?

Doesn't this go wrong if the user then inserts a new row in between 2 and 4 as all the formulas would be updated as follows:

i) the custom formula in A1 will become ROWSUM(2:5) which would be correct;

ii) the re-evulation of A1 will cause new formulas "=SUM(B2:B5)", "=SUM(C2:C5)","=SUM(D2:D5)" to be inserted into B1, C1 & D1 respctively which would be correct.

iii) BUT, depending on how the sheets formulas get updated when a row is inserted, the formulas in B1, C1 & D1 might then, in turn, be modified to respond to the change in range and become "=SUM(B2:B6)", "=SUM(C2:C6)","=SUM(D2:D6)" which would be wrong and would not correspond to the formula in A1.

So, I guess, I am asking whether the use of a custom formula will really do what I want.

The only other way I can think of is:

1 Define a custom Cell Type which simply displays its own formula as text, in this case, "=ROWSUM(2:4)";

2 When the user completes the A1 formula entry (how do I know this? EditModeOff ?), statements are executed which place the correct formulas into B1, C1 & D1.

3 If a new row were inserted between 2 & 4, the formulas in A1, B1, C1 & D1 would all be correctly updated and so would the displayed results in those cells.

But, I don't really know enough to know whether I am talking rubbish or simply do not understand enough!

Am I wrong to worry about the effect of inserting a new row on the formulas?

If so, is my alternative possible?

If neither will work, is there any other way?

Thanks

Richard