Date

Rickard

Hi!

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,

Rickard

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

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