Jun 26 2020 10:04 AM
Jun 26 2020 10:04 AM
Jun 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)..
Jun 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
Jun 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.
Jun 28 2020 09:40 AMSolution
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.
Jun 28 2020 09:21 PM
Jun 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.)
Sep 03 2020 08:47 AM
@mathetes Hi Mathetes! I'm hoping you don't mind helping me again. Same spreadsheet, trying to add more columns (3) before the due date. Good news is that if I add those columns on both tabs the data appears to carryover. Bad news is that it breaks the sorting function on the second tab. Any and all insights greatly appreciated!
Sep 03 2020 09:09 AM
Change the 3, toward the very end of the SORT function, to a 6
=SORT(FILTER(Assignments,(Assignments[DUE DATE]>=Start_date)*(Assignments[DUE DATE]<=End_date)),6)
That number denotes the column on which the sort order is to be based. So now it's the sixth column rather than the third.
Sep 03 2020 11:45 AM
@mathetes Gah - something's gone wrong (I'm sure it's user error but I can't figure out what I broke). When I click in a Due Date cell on the second tab so I can edit the formula, the populated area gets a blue square around it and the formula is grey and uneditable. I tried re-downloading the original file you created, same thing.
I'm losing my marbles
Sep 03 2020 01:18 PM
@mathetes Again, you are my hero. Interestingly though If for whatever reason I want to change the number of columns I use, it doesn't appear as if I can edit the formula. I've attached a screen grab so you can see I'm in a Due Date cell and the formula is gray and uneditable. Could my Excel program be corrupt???? Otherwise I have no idea why this would happen. I downloaded your file (again, you are a saint) and changed the end date but that was all I did.
On the original version, again, I downloaded and saved into a folder not previously used and this was the exact drama. I tried copying and pasting the original formula and got error messages, I tried copying and pasting the amended formula and got #value then #name then #calc errors on various attempts.
Is my computer cursed?
Sep 03 2020 02:02 PM
Your Excel program is fine.
These Dynamic Array functions end up producing results unlike the old fashioned ones. In this case, the ONLY operative function--i.e., the only one you can actually edit--is in the top left corner. It is not grayed out there. The results from a FILTER function do what's called spilling into adjacent rows and columns, and although the function appears in those adjacent rows and columns, it is always grayed out.
Any editing, therefore, that you might want to do on the due date itself (for example)--but this also applies to any of the other data--has to be done in the other sheet where the "raw data" appears. The sorted and filtered data is just that, a representation of the actual raw data that has been filtered (according to the criteria) and then sorted.... but it ain't there to be fiddled with.