Forum Discussion
Inconsistent formulas in table
JAnderson910 The problem is definitely a result of the formula referencing cell D30, which is the next row located outside of the table. When a new table row is inserted by using the Tab key, the relative cell reference is affected in the same manner that inserting a new row in the middle of the table would result in inconsistent cell references. One way to solve this problem is to define a name in Name Manager using a relative row reference...
Start by selecting cell H29, for example. On the ribbon, go to Formulas > Define Name. Name it "NextMeterRead", then in the Refers To field, select cell D30, then press F4 on your keyboard twice to make it a relative row reference ($D30). It should look something like this:
Define Name: NextMeterRead
Once defined, you can then update your calculated column formula as follows:
=IF(NextMeterRead-[@[Sump Meter]]>-1, NextMeterRead-[@[Sump Meter]], "")Or, as mentioned in my previous reply, the MAX function could also do the trick:
=MAX(NextMeterRead-[@[Sump Meter]], 0)NextMeterRead will always refer to the row below the current row, in Column D, regardless of how you go about inserting a new row. Cheers!
JAnderson910 The problem is definitely a result of the formula referencing cell D30, which is the next row located outside of the table. When a new table row is inserted by using the Tab key, the relative cell reference is affected in the same manner that inserting a new row in the middle of the table would result in inconsistent cell references. One way to solve this problem is to define a name in Name Manager using a relative row reference...
Start by selecting cell H29, for example. On the ribbon, go to Formulas > Define Name. Name it "NextMeterRead", then in the Refers To field, select cell D30, then press F4 on your keyboard twice to make it a relative row reference ($D30). It should look something like this:
Define Name: NextMeterRead
Once defined, you can then update your calculated column formula as follows:
=IF(NextMeterRead-[@[Sump Meter]]>-1, NextMeterRead-[@[Sump Meter]], "")
Or, as mentioned in my previous reply, the MAX function could also do the trick:
=MAX(NextMeterRead-[@[Sump Meter]], 0)
NextMeterRead will always refer to the row below the current row, in Column D, regardless of how you go about inserting a new row. Cheers!
