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?

 

[West Man]

So, you want the last 2 columns of data even if the dates do not match?
My last formula was based on matching the dates.
My first formula was based on the last column used although the match range in your version of it should be $1:$1 to avoid a problem with cells without data (assuming there is always a date in the header row when there is data in the column)
How can I, specifically, help you further?

 

[KyleG]

help still needed.

The formulas work great save for the fact that i need to be able to differentiate between a score of 0 and a did not work to generate a score eg. the bolded 0's. Ideally i would replace the bolded 0's with N/A but they dont carry over with the formulas.

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	17/12/2006	18/12/2006			DAY	17/12/2006	18/12/2006			DAY	17/12/2006	18/12/2006
11	Unders/Overs	0.00	0.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	17/12/2006	18/12/2006			DAY	17/12/2006	18/12/2006			DAY	17/12/2006	18/12/2006
18	Unders/Overs	0.40	1.80			Unders/Overs	0.00	0.00			Unders/Overs	0.00	0.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

Daily Und-Ovr.xls
	A	B	C	D
1	Name	Draw	17/12/06	18/12/06
2	Mathew S	1	$0.00	-$2.20
3	Rebecca S	2	$0.00	$0.00
4	Jacob S	3	$0.00	$0.00
5	Kieran B	4	$0.40	$1.80
6	Ben L	5	$0.00	$0.00
7	KatyW	6	$0.00	$0.00
Overs-Unders (2)

 

[West Man]

If you use N/A in place of the bolded zeros, the mixture of text and numbers in the matching array causes problems. I'm sure there is a way around this, but I took a slightly different approach, that being determining the column by matching the date. this also eliminates the need for subtracting 1 from the column for the first date. Try in B11 the following: =INDEX('Overs-Unders'!$2:$2,MATCH(B10,'Overs-Unders'!$1:$1,0))

 

[KyleG]

I could kiss you!

Ok maybe not that far, but i would so shake your hand for that one.
Thanks for all your help. I can finally finish this spreadsheet and put it in the hands of the user and forget about it.

Thanks again

Kyle