Solved: 6 month moving tracker

[Klartigue]

See the excel sheet attached.

The first sheet, named data, has my data.
On the second sheet, named tracking, i would like to track the 6 month increase or decrease in yield. So if you go the tracking sheet, as you see in cell B3, i have B3-B2 from the data sheet. However, If i insert a new date on the data sheet in cell B2(while everything else shifts down one), the formula on the tracking sheet shifts down even if I use absolute values. Is there a way to create a rolling formula so everytime i enter new data in row B2 on the data sheet, the tracker calculates that and does not follow the cells it was tracking previously?

Basically, for the most recent 6 months, i would like to caculate the difference between each day. And have this be a rolling calculation so it updates automatically.

Thanks for the help!

[Bob Phillips]

You can use

=INDIRECT("Data!B3")-INDIRECT("Data!B2")

or

=INDEX(Data!B:B,3)-INDEX(Data!B:B,2)

[Klartigue]

I tried using the =index reference. Please see my attached document, i made some notes in the 6 month tracker sheet they may help us reach my goal.. Thanks!!

[Klartigue]

I have also attached a document with ideas of creating a tracker using the =offset reference. See sheet 2 named 6mo track and see the references in cell D2 and my notes in the text box..

[Bob Phillips]

Try this

=INDEX('AAA Go'!B:B,ROW())-INDEX('AAA Go'!B:B,ROW()-1)

[Klartigue]

That works great! I posted a spreadsheet of the results. And if I enter a new line of data in row 2 of AAA Go then the rows in 6month tracker shift down accordingly and references work too!! Thanks for the help!!

[Bob Phillips]

You could suppress zeroes on that worksheet, and have negatives in say red, it helps pick out the changes better.

[Klartigue]

I I have the negative ones in red. Is there a way to get the positive ones to green?

[Bob Phillips]

Use a custom format of

[Green]0.00;[Red]0.00;

[Klartigue]

thanks!!