Change a Replicated Formula in a table?

[Smokeyham]

Hello,

I have a table with a formula in a column which replicates each time I add a new row. I recently have changed the formula and want to use the new formula for future records, but retain the old formula for records which have already been entered. I.e. I want Excel to replicate the new formula, not the old one.

When I add a new row in the table, Excel replicates the old formula, and ignores the new formula which I placed in the rows above my new row. With each new row I get the "This cell is inconsistent with the Column Formula" warning notice.

Any suggestions on how I can tell Excel to use (replicate) the new formula when I insert a new row? I have added about 10 new rows with the "new" formula.

Thanks for any help.

Gordon

 

[oldbrewer]

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]1.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]1.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]1.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]2.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]2.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]3.1[/TD]
[TD][/TD]
[TD="colspan: 5"]down to h formula was number above plus 0.3[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]3.5[/TD]
[TD][/TD]
[TD="colspan: 3"]from I I changed it to plus 0.4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD="align: right"]3.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD="align: right"]4.3[/TD]
[TD][/TD]
[TD="colspan: 2"]and dragged it down[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD="align: right"]4.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD="align: right"]5.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD="align: right"]5.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]o[/TD]
[TD="align: right"]5.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p[/TD]
[TD="align: right"]6.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Smokeyham]

Thanks for the response. I believe you were suggesting I drag the new formula down in the table, to create a new formula to be replicated when I add an additional row? I have tried that, and I just get the "Inconsistent formula" message, and then the next new row which I add within my defined table still replicates the original formula (not the new formula).

Thanks anyway though!

Gordon

 

[oldbrewer]

my formula checks the first column and if it is not blank applies the formula - when you change the formula you drag down from that row

 

[Smokeyham]

my formula checks the first column and if it is not blank applies the formula - when you change the formula you drag down from that row

Thanks for the response. The issue that I have is that when I drag down from the last formula entered it does not copy the NEW formula, but looks back up the column and copies the original version of the formula. I would like to have Excel copy the latest version of the formula automatically.

 

[Joyner]

Hello, If I understand correctly what you want to do, I'm not sure you can with the way Tables work, but maybe someone can show differently.

One way you may be able to accomplish what you want is to use a helper column in the Table with values, 1 and 2 (add 3 and so on if the formula changes again) and use the CHOOSE Function to "Store" your formulas - if 1 use the first formula, if 2 use your new formula, etc.

Not ideal but I believe it will accomplish what you want.

 

[oldbrewer]

my way does not define a table but adds a new formula every time A column eg is populated - once the formula in eg B col is changed, it is the changed formula that is replicated