Rickard ✭✭
edited 12/09/19 in Formulas and Functions


I have a tricky formula I'm trying to calculate, but with no success so far... 


I have to different sheets that I'm looking on. 

Sheet1: Planning and monitoring sheet

Sheet2: Work reports

The formula challenge:

In Sheet2 our guys at the field are reporting on different predetermined projects. 

They report which date they have been working with the different projects, amount of hours + if the project is finished or not (Yes/No). 


In Sheet1 I want not just to sum up how many hours we have spent on each project, but I want also the date column "Finished" to automatically show which date the project got finished. 


So, is there a way to put a formula into the date cell in Sheet2 that will show which date the guys have reported as finished ("Yes") in Sheet1 ? 

The criteria must be: if the project nr is the same + if the column "Finished" is marked "Yes" in Sheet1


Any ideas?


Best regards,




  • L_123
    L_123 ✭✭✭✭✭✭

    i'm not entirely sure I understand, some pictures might help, publishing a copy here would definitely help me understand.


    That said if you want to track the amount of time spent on a project it depends on how you have your sheet formatted. You could possibly use a sumif formula, a collect formula, or maybe even a sum(Children()) style formula depending on the arrangement of the sheet.


    To parse out the finish date you will need either a vlookup() formula or an index(match()) formula. Again, I need to know more about the sheet to give you a better answer.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The date would have to be manually entered on sheet one. Then a simple INDEX/MATCH would pull it to sheet 2.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!