Drawing row linked data into another sheet together?


Hi All!

So I have a sheet with a list of tasks, a checkbox for completion and a due date. In one of my other metrics sheet, I am trying to generate a list of my top 5 overdue tasks.

I have managed to get a list of the most overdue dates with a SMALL(COLLECT...) function which will only count dates that are not listed against a task which has been completed, i.e a ticked checkbox.

What I'm struggling with now is getting the cell next to this to display the task name housed in the same row as the overdue completion date in the reference sheet.This is the best I've been able to come up with from some searching through related threads:

 =VLOOKUP([Date that my SMALL/COLLECT Formula has identified as the most overdue]3, {Reference Sheet Look up Table which includes the task name and due date columns}, 2, false)

The only problem with this though, is that if I have two overdue tasks that should have been completed on the same day, it's only showing me the first task description which it finds that matches this date, not the row specific one. So I get the same task name repeated next to overdue dates that relate to different tasks.

I know that this is easily generated in a report, however having a report of this nature for every project which my team is working on will get very cluttered quickly, and it would be much preferable to have this set up in my metrics sheet alongside all the other data.

I have tried to play around with the INDEX and MATCH functions, however am still new to smartsheets and can't seem to make anything work. 

I would be very grateful for any help/suggestions! smiley





Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!