Changing Cell Formula Each Week

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
When you say "until next week", what happens when you go past week 52 of a year?
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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?
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
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)
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

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
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
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.
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623

ADVERTISEMENT

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?
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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.
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
How about:
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)))
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
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
 

Forum statistics

Threads
1,136,366
Messages
5,675,354
Members
419,564
Latest member
Phil57

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