Complicated formula -help appreciated

[LizGr]

Hi all! I’ve downloaded the student assignment planner template. One tab is for assignments with a column for assignment name and another column for assignment due date. No prob!

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????

[#2]

@LizGr 

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)..

[#3]

@mathetes 

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

[#4]

@LizGr 

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.

[#5 · Accepted solution]

@LizGr 

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.

[#6]

Thank you! I’ll wrap my head around this Monday (just have the phone over the weekend) And happy anniversary!!!!

[#7]

@mathetes 

THANK YOU! This is super helpful and certainly nothing I could have done on my own! 

[#8]

@LizGr 

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.) 

[#9]

@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!

[#10]

@LizGr 

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.

[#11]

@mathetes I need your mailing address so I can send you a thank you card 🙂

[#12]

@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 😞

[#13]

@LizGr 

Here's the one I revised to add those three columns. If you can adapt from it, go ahead. Otherwise, please post the one that's giving you troubles.

[#14]

@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?

[#15]

@LizGr 

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.

[#16]

@mathetes Been out of town but wanted to truly thank you from the bottom of my heart for all your assistance. You have been so kind and patient and helpful - THANK YOU!!!

[#17]

@LizGr 

You're very welcome. Have a good year in school. A safe, and healthy, year.

[#18]

@mathetes It appears you understand this stuff perfectly so I wanted to chime in with another question relative to this template.  I really like the calendar view but just like the other person you were helping I don't want the calendar view to show "Assignment Due", I want it to show the exact text that appears in the 1st tab where assignments are entered.  Any assistance would be greatly appreciated.  Thank you!

[#19]

@DMWinkler 

Are you talking about the same file? I've attached the one we worked on before (two years ago). So far as I can tell, it does display the same text as that entered on the other sheet.

[#20]

@mathetes The only difference is that I would like the calendar view vs the tabular view.  So on 1 tab I enter the assignments and the dates and then the monthly calendar shows (in calendar view) the assignment details on the dates they are due.  Thanks for helping.