Simple question about tables

[sgcannon]

I have been using Excel for many years but have never used tables. For my first foray into the topic I created a simple table that tracks my exercise and weight loss. It is supposed to automatically create a new row for each day whenever I enter new data. But the formula is not updating in one column. This column is supposed to find my weight from 7 days ago and subtract it from my current weight to give me a one week weight loss. Obviously, for the first 7 rows of the table, I want the cell to be "". There are a lot of ways I could do this but I thought that the simplest would be:

=IF(TODAY()<[@Date],"",IF(ROW()<10,"",[@Weight]-IF(ROW()<10,D$3,D3)))

The D$3 is just so that I don't reference a cell location that doesn't exist because it has a negative row.

But this formula doesn't update each time I add a new row. I am suspecting that it has to do with the lack of a structured reference in the D$3,D3 but since I have never used structured references before, I'm really not sure how to use a structured reference to go back 7 rows.

Thanks for your help!

Steve

 

[Norie]

Steve

When I set up a simple table with 3 columns, one for Date, one for Weight and one with the formula i posted it's working fine, i.e. the formula is copied down when new data is added.

Is there anything else going in the workbook?

If you rebuild the table does it work properly?

 

[sgcannon]

Thanks for your help. I realized what my problem was and I'm embarrassed to say that I had set up this column by writing a formula way down in the worksheet and copying up. The result was that while my formula worked in the higher rows, in the lower rows I was getting a #REF because I was pointing to negative columns. I did not notice the problem initially because the formula for those cells had an output of "", exactly how I wanted, but obscuring the fact that excel wasn't happy with the formula.

Steve