Date affected cell referencing

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 }
 
A

Alain CROS

Hi.

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

Alain CROS.
 
H

Harlan Grove

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