Simple question about tables

sgcannon

Board Regular
Joined
Jan 28, 2016
Messages
55
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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?
 
Solution

sgcannon

Board Regular
Joined
Jan 28, 2016
Messages
55
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,599
Members
417,154
Latest member
gm_jagath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top