# Changing Cell Formula Each Week

#### KyleG

##### Well-known Member
I have a sheet (sheet2) that this week has the formula
Cell B2
=Sheet1!D2
Cell B3
=Sheet1!B2

Next week I want the formula to be
Cell B2
=Sheet1!F2
Cell B3
=Sheet1!D2

And so forth.

Each Column has the Week ending date (a sunday) in Row 1. So D2 represents this week and B2 Last week, until next week when D2 becomes the 'last week' and F2 becomes the this week.

The inbetween letters contain another set of data for those weeks so i will apply the same formula to these.

Is it possible to have the cells formula change each week?

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
When you say "until next week", what happens when you go past week 52 of a year?

If you want sheet2 B2 to always have the current week ending date and B3 to have the previous week ending date, there must be some method of determining the column representing the current week. If the week headings are not prepopulated, then the column could be identified by finding the last cell in the heading row that has a value. Or, perhaps if there is data for the current week in row 2 but no data for future weeks, then the column could be identified by finding the last cell in row 2 that has a value.

Do either of these situations exist? If you would ignore the date headings, is there another way to visually identify the column having the current week?

The header rows will not be prepopulated so that is one option. In fact only the most recent data will be featured (eg. no columns for future data have any data)

See if this works for you

For B2 =INDEX(Sheet1!1:1,MATCH(9.999999E+307,Sheet1!1:1))

And for B3 =INDEX(Sheet1!1:1,MATCH(9.999999E+307,Sheet1!1:1)-1)

Format both cells as date

tinkering...

West Man is the man,

=INDEX('Overs-Unders'!\$2:\$2,MATCH(9.999999E+307,'Overs-Unders'!\$2:\$2))

We have discovered that we want the cell to return the text, N/A, if it is in the original cell because we need 0 to mean 0 and a blank or N/A is not the same as 0. Is that possible? if not we will just have to live without it.

heres an example

This is what i need to be able to do.
Daily Und-Ovr.xls
ABCDE
1NameDraw20/11/0621/11/0622/11/06
2Mathew S1\$0.00\$0.30
3Rebecca S2\$0.00-\$10.00
4Jacob S3\$0.00\$0.10\$0.00
5Kieran B4\$0.30\$0.00
Overs-Unders

but it makes my calculations incorrect

Mathew S unders/overs should display N/A or blank and then 0.30
Rebecca S unders/overs should display -10.00 and then N/A.

Does that help anyone help me?

It appears that the formula is failing because there is no data in E3 in the Overs-Unders sheet. After seeing your data, I can see that there are better approaches than I suggested. If I have time today, I will work on it, but others may beat me to it, likely with better solutions. I am far from an expert but try to help because I get much from this forum and feel I should give back when possible.

In B11 =IF(INDEX('overs-unders'!\$C2:\$E5,\$C\$9,MATCH(B10,'overs-unders'!\$C\$1:\$E\$1))=0,"N/A",INDEX('overs-unders'!\$C2:\$E5,\$C\$9,MATCH(B10,'overs-unders'!\$C\$1:\$E\$1)))

And in G11 =IF(INDEX('overs-unders'!\$C2:\$E5,\$G\$9,MATCH(F10,'overs-unders'!\$C\$1:\$E\$1))=0,"N/A",INDEX('overs-unders'!\$C2:\$E5,\$G\$9,MATCH(F10,'overs-unders'!\$C\$1:\$E\$1)))

hmmm

Ok so i just want to show you more of my feedback page. I can't show you more but i can tell you that these boxes repeat until they cover all the cells on the unders-overs sheet, 92.
Maybe I've been looking a this wrong and what i need is a formula that looks at the last cell in row 1 of unders-overs with text. This returns a column. then the row is preset.

So Feedback c11 will always get the value from row 2 (unders-overs) and always the last column. B11 will always get the value from row 2(u-o) and always the last column-1.
H11 - row3
M11 - row4
c18 - row5
etc

Replies
1
Views
196
Replies
6
Views
314
Replies
1
Views
434
Replies
2
Views
376
Replies
2
Views
280

1,218,808
Messages
6,144,599
Members
450,555
Latest member
sheldor

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