Current Week Course and Assignment

[Billy Spivy]

Hello,

I tried to populate the Assignment cell D12 of Current Week View sheet but this formula isn't working. Does anyone know why not? My objective is to populate the cells C12:L21.

Thank you for any help or suggestions,
Billy

Please Login or Register  to view this content.

[Glenn Kennedy]

You had two named ranges WkYear. I killed one and then had to kill the other as Excel didn't "like"it. It's now re-instated. the other problem was C10. That is a full date... 19/3/18. If you want to select ONLY the day, then you need to amend the formula:

=IFERROR(INDEX(Assignments!$C:$C,SMALL(IF(Assignments[Due Date]=DATE(WkYear,WkMonthNum,DAY(C$10)),ROW(Assignments[Assignment])),ROWS($1:1))),"")

Don't forget that this is an array formula and needs CTRL-SHIFT-Enter

[Glenn Kennedy]

That said... do you really need the WkYear thing at all?

=IFERROR(INDEX(Assignments!$C:$C,SMALL(IF(Assignments[Due Date]=C$10),ROW(Assignments[Assignment])),ROWS($1:1))),"")

[Billy Spivy]

Thanks Glenn for correcting. I was able to put that in column C by just changing the $C:$C to $B:$B so it will the associated Course for that date.

How to copy across?

[Billy Spivy]

The #3 gives an error

[Billy Spivy]

I copied the first 2 cells and pasted across. Then dragged down each column and it seemed to work.

Thank you Glenn

[Glenn Kennedy]

One ) too many..

=IFERROR(INDEX(Assignments!$C:$C,SMALL(IF(Assignments[Due Date]=C$10,ROW(Assignments[Assignment])),ROWS($1:1))),"")

[Billy Spivy]

Okay got it thanks again Glenn.