IF formula for column

[trinaren]

Pleas help me before I throw my computer out the window!!!

I have a worksheet with a number of sheets. Each sheet is devoted to a specific 'project' my boss is working on and lists all the tasks that are required to complete the project. The columns include

Task, Person responsible, target completion date, status

I want to have a summary page where it shows all the projects and what task it is on and who is responsible.

I've figured out that if I use formula =if("sheet1D2="in progress", Sheet1A1) and =if("sheet1D2="in progress",Sheet1B1) will give me the task and person responsible.

The problem I'm having is that I can't figure out how to make the formula apply to the Task or Person Responsible column. I won't always know what row is "in progress" unless I do it manually (which completely misses the point of having the formula).

I didn't think that it would be difficult, but I just can't seem to get it!

 

[HackSlash]

The easiest way to analyze data is to have it match a pattern. Your required pattern is that "In Progess" must always be in cell "sheet1D2". If you cannot modify the data to fit your required pattern then you need to look in to using a search by column or row using VLOOKUP or HLOOKUP.

https://support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1