Making a list based on week (offset if)

[Kinver]

Hi

I'm putting a summary sheet together for a rollout of work across several regions. The first table was fine, just showing overdue sites based on the 'required by' date:

{=OFFSET(WorkItOut!$A$1,SMALL(IF((WorkItOut!$L$1:$L$655=0),IF(WorkItOut!$A$1:$A$655<TODAY()-1,IF(WorkItOut!$A$1:$A$655>0,ROW(WorkItOut!$A$1:$A$655)-MIN(ROW(WorkItOut!$A$1:$A$655))+1))),ROWS(WorkItOut!$A$1:$L1))-1,0)}

(Might be a little messy)

However the next tables I want to use are for upcoming dates, i.e. Sites this week, Sites next week & Sites the week after. I think it probably needs to reference the Required By date in Column 1, reference the week number for that date and make a list... I just haven't done this with OFFSET before and the results I'm getting back are less than satisfactory.

Any and all assistance warmly received.

 

[Kinver]

... Right

I've managed to sort the 'this week' array using WEEKNUM (NOW()) as per the below....

I think embarassingly - i need pointers on how to change (NOW))) to next week and the week after...


=OFFSET(WorkItOut!$A$1,SMALL(IF((WorkItOut!$L$1:$L$655=0),IF(WorkItOut!$M$1:$M$655=WEEKNUM(NOW()),IF(WorkItOut!$A$1:$A$655>0,ROW(WorkItOut!$A$1:$A$655)-MIN(ROW(WorkItOut!$A$1:$A$655))+1))),ROWS(WorkItOut!$A$1:$L1))-1,0)

 

[Kinver]

OK - what we have here is a lesson that sometimes engaging brain helps.

Just in case this helps someone else... Next week equals a +1 after WEEKNUM(NOW()), unsurprisingly +2 gives you the week after. Cheers.

=OFFSET(WorkItOut!$A$1,SMALL(IF((WorkItOut!$L$1:$L$655=0),IF(WorkItOut!$M$1:$M$655=WEEKNUM(NOW())+1,IF(WorkItOut!$A$1:$A$6 55>0,ROW(WorkItOut!$A$1:$A$655)-MIN(ROW(WorkItOut!$A$1:$A$655))+1))),ROWS(WorkItOut!$A$1:$L1))-1,0)