Date affected cell referencing

[john stevens]

is it possible to make excel work on a rolling four week
rota, whereby excel knows the current days date, will know
which week we are on (week1, week2, 3, 4) and then pull
information from another sheet based on which week it is.
example:-

{if (todays date = 12/07/03 then week = week 1) }
{ }
{ cell = sheetname cell b4 }

 

[Alain CROS]

Hi.

Like that ?
date = 12/07/03 in C2
=INDIRECT("Sheet"&MOD(WEEKNUM(C2),4)&"!B4")

Alain CROS.

 

[Harlan Grove]

Like that ?
date = 12/07/03 in C2
=INDIRECT("Sheet"&MOD(WEEKNUM(C2),4)&"!B4")

....

Close, but MOD(x,4) for x in {1,2,3,4} returns {1,2,3,0}. Change your MOD
expression to (1+MOD(WEEKNUM(C2)-1,4)).