# Is it possible to create a formula that changes which cells it reads over time?

#### s86m

##### New Member
Hi All,

I am currently trying to create a Spreadsheet that can track the value of items over time. I have a list of about twenty items in rows and each column represents a week. Each week I will add new data into the spreadsheet and so over time it will become more populated with data.

I want to be able to automate a process that looks at the percentage change of each item over a one week, one month, and year to date time period (in three separate formulas), however I have been trying to determine if it is possible for the formula to know which week is current and amend which cells it looks at accordingly.

For example in the table below to look at % change between rows B & C I would use the formula (C-B)/B but is there a way so that next week the formula would change to (D-C)/C and so on as time progresses. I thought about using some sort of IF statement and a TODAY for example (IF TODAY > C2 (D-C)/C, IF TODAY < C2 (C-B)/B however trying to figure out that across a 52 week period sounded messy and I hope there would be a better way.

 1 A B C D E 2 01/01/21 08/01/21 15/01/21 % Change 3 Item 1 1.00 1.25 1.15 =sum(C3-B3)/B3 4 Item 2 0.5 0.75 1 =sum(C4-B4)/B4

Similarly with the one month period I was thinking that I would want the formula to look at the most recent column of data and the 4th most recent column of data, and calculate a % change based on that. In this scenario the original formula may be (E-A)/A for wk 1, then (F-B)/B for wk 2. Again is there a way to automate this?

Finally for a year to date time period I thought that there will be a constant from Wk1 but then it will update as weeks progress so it would be (B-A)/A, (C-A)/A, (D/A)/A and so on.

Hopefully this all makes sense. If anyone has any ideas that would be great.

Thanks for the help!

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### aRandomHelper

##### Board Regular
For your first problem, maybe you can try something like this:
1.xlsm
ABCDE
101-01-2108-01-2115-01-21% Change
2Item 111.251.1525%
3Item 20.50.75150%
Sheet1
Cell Formulas
RangeFormula
E2:E3E2=(OFFSET(E2,,-2)-OFFSET(E2,,-3))/OFFSET(E2,,-3)

And if you insert a new column for next week:
1.xlsm
ABCDEF
101-01-2108-01-2115-01-21% Change
2Item 111.251.15-8%
3Item 20.50.75133%
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=(OFFSET(F2,,-2)-OFFSET(F2,,-3))/OFFSET(F2,,-3)

Replies
5
Views
168
Replies
4
Views
352
Replies
1
Views
73
Replies
13
Views
102
Replies
5
Views
110

1,127,505
Messages
5,625,205
Members
416,080
Latest member
blemon

### 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.

### Which adblocker are you using?

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

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