Forum Discussion
Complicated formula -help appreciated
The next tab is a calendar. There’s a complex if formula that basically says if the assignment due date matches the calendar date then the calendar populates with “assignment due”
However I’d like it to populate with the assignment description that goes with the date.
I can update the formula so it does that but I have to do that manually with each assignment. And if I sort the assignments (either alphabetically by description or chronological by due date) the calendar changes since the description is hard coded in the formula.
Can anyone solve this conundrum????
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.
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)..
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
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.
