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 I was unable to duplicate the described problem. Please clarify the following:
- Is the table located on the same sheet as the cells referenced in the formula ('Water Usage')? Or a different sheet?
- What is the address of the cell that contains the formula =IF('Water Usage'!D28-'Water Usage'!$D27<0, 0, 'Water Usage'!D28-'Water Usage'!$D27)?
- Is the formula consistent throughout the entire column?
It's possible the formula in the calculated column just needs to be "refreshed". Select any cell in the column that contains the correct formula, press F2 on your keyboard (or click into the formula bar), then press Enter. If the "AutoCorrect Options" icon appears, click it and select "Overwrite all cells in this column with this formula".
Formula Tip:
=IF('Water Usage'!D29-'Water Usage'!$D28<0, 0, 'Water Usage'!D29-'Water Usage'!$D28)
...can be simplified using the MAX function:
=MAX('Water Usage'!D29-'Water Usage'!$D28, 0)
- Everything for the formula is on a single sheet inside a table. The formula functions properly when I enter the new data on the row right below the table but if I use the TAB key to create a new entry the formula becomes inconsistent. This can get annoying since I can't use the TAB key when entering new lines I have to use the mouse to click on the row below my last entry.
Using the tab key to start a new row for entry. Formula skips the new row.
Here I started a new row without using the tab by just entering data in the row below the table. here the formula stays consistent.
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!
