Date affected cell referencing

  • Thread starter Thread starter john stevens
  • Start date Start date
J

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 }
 
Hi.

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

Alain CROS.
 
Alain CROS said:
Like that ?
date = 12/07/03 in C2
=INDIRECT("Sheet"&MOD(WEEKNUM(C2),4)&"!B4")
Click to expand...
....

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)).
 
Back
Top