06-26-2020 10:04 AM
06-26-2020 10:04 AM
06-26-2020 11:18 AM
Are you "the student" in this situation? Or a teacher?
I have some ideas, but not enough knowledge from your description to be able to really be helpful. Is it possible for you to post a sample of the spreadsheet--that it's a template is good to know, but I don't think any of us here is familiar with all the templates.
If you want to try to solve it yourself, it may be possible to use the VLOOKUP function to retrieve the description for the calendar page. Although that would also require something more than just the date as an identifier, since you very well might have two or more assignments for any given date.
This is why seeing the actual workbook (not just an image, but the actual workbook, just so long as there's no confidential info or private info in it)..
06-26-2020 12:10 PM
OMG thank you for replying. I've attached the file. The formula I'm trying to edit is
=IF(LEN(F6)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,@MoMonthNum,F6))>0,"Assignment due!",""))
I was hoping to change the "Assignment due!" to pick up the Assignments tab Column B cell that corresponds with the appropriate due date
06-26-2020 02:11 PM
One of the problems with templates (in general) is that they often emphasize sizzle over functionality. In this case, for example, the result is a pretty calendar, but all it says is "assignment due" and not the actual assignment. The formula would need a lot of modification to be able to say what the assignment is and especially if there were two or more on the same date.
So I have a totally different way I'd do this, but as it's my wedding anniversary and we're going out to dinner, I'm not going to be able to do it until later today or even later this weekend. A question, though, for you: if instead of a "pretty calendar" layout, if you just had a list of the assignments due, naming them and giving the dates in order...i.e, a functional output that was actually helpful...would that meet your need?
In order to make it work, you need to be working with the latest Excel version, one that can handle the functions FILTER and SORT...so what happens if you enter =FILTER into a cell? Does it prompt you for the rest of the variables... if so, we're in business.
06-28-2020 09:40 AM
I hope you haven't lost interest in your original request. As you'll see in the attached, I carried through on my promise to deliver a wholly different way to do this.
I've deleted the tabs that gave a monthly or weekly calendar with the anemic "Assignment due" note where it applied.
In their place, I created a tab called "Sorted Order" which, as the name implies, lists the assignments in sorted order by date. And, when you look at the sheet itself, you'll see that I give you the ability to set a start and end date for the list, so you can determine whether it shows just the coming week, the coming month, the coming semester....whatever. You can also list assignments past.
And I've added a column in which you can make notes on the magnitude of the assignment, so that (if you choose to use it this way) you can get working on things due a month out if they require a lot of work.
It would also be fairly easy to add yet another column that just reflects Priority or Importance, and do the sort such that the top priority are listed first regardless of due dates.
Again, I need to note that the SORT and FILTER functions work only on the most recent versions of Excel, so if you're not seeing the dynamic results of changing the start and end dates, that's an indication you don't have the required version of Excel.
06-28-2020 09:21 PM
06-29-2020 02:40 PM
You're very welcome.
As I said, there are ways it could be refined further...such as adding other columns that reflect things like priority or importance, whether it's a course for your major, other characteristics you might want to be tracking.
Could even add a date column where you give yourself not only the date the assignment is due to be turned in, but dates to start working on a major paper. (I remember back in my college days suddenly realizing on a weekend that a major paper was due the following Tuesday. I'd been researching it, but hadn't started writing. And this was in the days of typewriters, not even electric typewriters. That's how old I am.)
by WillMcDowell on April 16, 2020