Drawing row linked data into another sheet together?

GracePendlebury

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! 

Thanks,

Grace

Andrée Starå

Hi Grace,

Did you get this solved?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

GracePendlebury

Hi Andrée,

I seem to have had a glitch when I started this discussion; after I clicked post neither I or other users I'm in touch with could find it in the community or my profile. As such I started a new one to the same effect where some extremely helpful people are trying to help me get this solved, which I haven't managed to do yet:

https://community.smartsheet.com/discussion/linking-row-data-together-new-sheet

Weirdly, I could see this discussion this morning when I logged in at the start of another work week. Not sure what's happened here. 

Thanks,

Grace

Andrée Starå

Happy to help!

Something posts get marked, so they have to approve manually, and that's probably that's what happened.

I'll take a look at the other post and see if I can help!