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?