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
=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