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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Picking up data from workbooks which may not exist yet 3
Tie a Calendar week to a Scoped Projected Week 3
SumIf statement: incrementing 4
Excel Excel: sum of 7 cells (weeks) in every row of a spreadsheet 1
Month, DATE, Week 7
Print Week Category based on range for month 4
Due date - change cell color 2
Modify dates byr referencing a cell name 3

Back
Top