Changing Cell Formula Each Week

[KyleG]

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?

 

[GlennUK]

When you say "until next week", what happens when you go past week 52 of a year?

 

[West Man]

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]

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]

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]

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]

heres an example

This is what i need to be able to do.

Daily Und-Ovr.xls
	A	B	C	D	E
1	Name	Draw	20/11/06	21/11/06	22/11/06
2	Mathew S	1	$0.00		$0.30
3	Rebecca S	2	$0.00	-$10.00
4	Jacob S	3	$0.00	$0.10	$0.00
5	Kieran B	4	$0.30		$0.00
Overs-Unders

but it makes my calculations incorrect

Daily Und-Ovr.xls
	A	B	C	D	E	F	G	H	I
9	Mathew S		1			Rebecca S		2
10	DAY	21/11/2006	22/11/2006			DAY	21/11/2006	22/11/2006
11	Unders/Overs	0.00	0.30			Unders/Overs	0.00	-10.00
12	Welcome to your new Feedback form. Please ask a supervisor if you have any questions.					Welcome to your new Feedback form. Please ask a supervisor if you have any questions.
13
14
Feedback

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]

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]

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]

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

Daily Und-Ovr.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
9	Mathew S		1			Rebecca S		2			Jacob S		3
10	DAY	25/11/2006	26/11/2006			DAY	25/11/2006	26/11/2006			DAY	25/11/2006	26/11/2006
11	Unders/Overs	0.00	6.00			Unders/Overs	0.00	0.00			Unders/Overs	0.00	0.00
12	Thank you for your support over the past week while we are working out the kinks in our new system.					Thank you for your support over the past week while we are working out the kinks in our new system.					Thank you for your support over the past week while we are working out the kinks in our new system.
13
14
15
16	Kieran B		4			Ben L		5			KatyW		6
17	DAY	25/11/2006	26/11/2006			DAY	25/11/2006	26/11/2006			DAY	25/11/2006	26/11/2006
18	Unders/Overs	0.00	0.10			Unders/Overs	0.00	0.00			Unders/Overs	0.00	20.00
19	Thank you for your support over the past week while we are working out the kinks in our new system.					Thank you for your support over the past week while we are working out the kinks in our new system.					Thank you for your support over the past week while we are working out the kinks in our new system.
20
Feedback