Lookup tasks and sort by date and exclude completed tasks

I have a google doc I'd like to use to organize some tasks and show them easily.

I need a sheet to show the tasks ordered by date, but exclude tasks that have a completion date. If the date is today's date, highlight the cell in green or give it some formatting to show it's today's task to be completed.

https://docs.google.com/spreadsheets...it?usp=sharing

I've made 5 sheets. Task View to create the formulas on, 3 sheets of user data and 1 sheet that shows what it should look like if the formulas work correctly.

Can this be done?

To attach an Excel file to your post,

• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Here is my attachment as a .xlsx file.

If you want to use formulas for this, I would recommend keeping your data (in the User1, 2, and 3 sheets) sorted by Task Date, Newest to Oldest. Otherwise, you will have to use helper columns.

Then, you can use these:

A2 =IFERROR(INDEX(User1!B$2:B$20,SMALL(IF(User1!C$2:C$20="",ROW(User1!B$2:B$20)-(ROW(User1!B$2)-1)),ROWS(A$1:A1)))&"","") Ctrl Shift Enter
B2 =IFERROR(INDEX(User2!B$2:B$20,SMALL(IF(User2!C$2:C$20="",ROW(User2!B$2:B$20)-(ROW(User2!B$2)-1)),ROWS(A$1:A1)))&"","") Ctrl Shift Enter
C2 =IFERROR(INDEX(User3!B$2:B$20,SMALL(IF(User3!C$2:C$20="",ROW(User3!B$2:B$20)-(ROW(User3!B$2)-1)),ROWS(A$1:A1)))&"","") Ctrl Shift Enter

Dragged down until you get blank cells.

Note: If you change the data in the User sheets from ranges to tables, you can refer to the entire table column instead of a static range in the formulas.

I will show the steps to take for the Conditional Formatting in my next post.

To apply the Conditional Formatting, highlight A2:C20 (or however far you want to apply the formatting) > Conditional Formatting > New Rule > Use a formula

=INDEX(INDIRECT(A$1&"!$A$2:$A$20"),MATCH(A2,INDIRECT(A$1&"!$B$2:$B$20"),0))=TODAY()

Format: Fill green

See attachment for clarification.

I'll have a go at it. Is it possible to have multiple open (i.e. not completed) tasks in the same day?

Yes, some users have multiple tasks for the day, or multiple tasks with the same date, in which the order will n matter.

That greatly complicates things. I will have to take a deeper look at this and see if I can come up with anything.

Try this (see attachment).

Right now, it is set up assuming that each user will have no more than 100 open tasks. This is due to all formulas residing in rows 2 through 101.

I made the data in each User sheet into a table to account for future data.

Helper columns:
User1 E2 =IFERROR(LARGE(IF(Table1[Completion Date]="",Table1[Task Date]),ROWS(A$1:A1)),"") Ctrl Shift Enter
User1 F2 =IFERROR(INDEX(Table1[Task],SMALL(IF(Table1[Task Date]=E2,ROW(Table1[Task Date])-(ROW(A$2)-1)),COUNTIF(E$2:E2,E2))),"") Ctrl Shift Enter

Helper columns on the other two sheets are the same (just change Table1 to Table2 and Table3).

Then, in Task View
A2 =User1!F2
B2 =User2!F2
C2 =User3!F2

Conditional Formatting is the same. I just changed A to E, B to F, and 20 to 101.

CF formula: =INDEX(INDIRECT(A$1&"!$E$2:$E$101"),MATCH(A2,INDIRECT(A$1&"!$F$2:$F$101"),0))=TODAY()

Thank you so much for this! Do you know if there's an issue with any of the formula formatting working in Google Sheets or not? I'm having trouble getting it to work there.

You're welcome. I am not familiar with Google Sheets so unfortunately, I cannot help you there.

You can try to open up the workbook in Google Sheets and see what the formulas change to.

It looks like some of those formulas don't work in Google Sheets, but that's okay. The core part that we need does work so thank you again! You've been a huge help and we will use this going forward.

Great, I'm glad that we were able to help.

If that solved your original question, please mark this thread as SOLVED.

If you have any further questions about other platforms such as Google Sheets, consider creating a thread here: https://www.excelforum.com/for-other...mobile-os-etc/