Editing a Weekly Assignment Template

[bnc3417]

Hi All!

I downloaded the attached template to try to customize to use to help students get both short and long-term views of their upcoming assignements. This template is great, but the one thing I wish it did was instead of populating in the week and monthly views as "Assignment Due!" I would like it to populate with the name of the assignment that corresponds with the date.

I looked at the formula used to populate into the weekly and monthly views and it is was beyond my knowledge of Excel to try and adjust it. Is what I am asking for possible? Any help you could provide would be great!

[humdingaling]

ok tip for none US people dates are switched around
in the week view

Formula in B15 should be (MM/DD/YY)

Please Login or Register  to view this content.

works on my file as (international date standard DD/MM/YY)

Please Login or Register  to view this content.

the month view would be similiar idea of replacing "assignment due" with the index/match formula

[humdingaling]

Month View formula adjustment
in B7 fill across and copy into following weeks (b13,B19,B25,B31)

For US

Please Login or Register  to view this content.

(for international)

Please Login or Register  to view this content.

US version attached
if values show everywhere just click on new month and it should update

[bnc3417]

Thank you for the quick responses! I downloaded the US version you attached in the thread above and tried to change the name of an assignment but it returns in the month view as a "VALUE!" error. Again, I have no idea where I would begin adjusting this error. I changed the name of a few assignments in the assignments tab just so I can have an idea of how the month view would look without the error. Am I missing something?

[humdingaling]

ok made some slight changes to how the date is matched
using Date() instead of datevalue() which can be buggy

i switched my system to american date to make sure it worked this time

[bnc3417]

Humdingaling,

Thank you so much for your help! The calendar works exactly how I wanted it to. I just have one more issue: when I add two assignments that have the same due date, only the first assignment shows up on the calendar. I tried to "drag" the formula down in the other cells in that specific date but that didn't work. How would I be able to make it so that if more than one assignment is due on one day, they would both show up? I added another assignment in the assignments table with the same date as another assignment just to test it out. Is this possible?

[humdingaling]

to get the 2nd assignment in is actually not a simple task
i had to do some research to get it as i dont use it all the time

see attached

2nd row uses CSE (Ctrl+Shift+Enter)
so if you see the formula missing the {} around it
you need to press CSE to enter formula to fix it
play around with it and you will see what i mean

refer to http://office.microsoft.com/en-au/ex...001087290.aspx if you want more info on it

the principal can be applied to 3rd occurrence
two numbers just need to be changed

=IF(LEN(F24)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,MoMonthNum,F24))>2,INDEX(Assignments[ASSIGNMENT],SMALL(IF(Assignments[DUE DATE]=DATE(MoYear,MoMonthNum,F24),ROW(Assignments[DUE DATE])),3)-4),""))

[krolo]

hello, congratulations for this work with this template student assignment planner!

However I would ask some help with it, because I'm european and this template only works correctly when I change my region preferences to the united states. I think the main problem is related to date form because in europe we use the form day/month/year to dates. I tried to make some alterations to the excel formulas but it still not working correctly. Anybody knows how to rewrite the formulas in this template to the european form?

Best regards.

[JessicaG7]

Hello!

I know this is a very old post, but would you happen to have the international template file? I followed your instructions & tried to replicate it to make it work but it keeps showing me an error (#VALUE!), I'm starting to think it has to do with the fact that even thought my Excel is set up in english, my region foreing languaje is spanish. I tried translating all lookup tables and so on, but still, couldn't make it work. Any thoughts?

Thanks!

[alansidman]

@JessicaG7

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread