IF contingent task closed, THEN change formatting of dependent task cell

**tek_9** · 10-22-2012, 08:31 PM

hello,

I have an array like the following:

ID	Work Item	Blocked By	Assigned	start (plan)	start (actual)	end (plan)	end (actual)
0	test button	1	tom	10/13	10/13	10/20	10/20
1	make button	...	jim	9/20	9/23	9/30	9/30
2	...	...	...	...	...	...	...

IF the blocked column "C" has a value,
AND that value is in the ID column "A"
AND it has a corresponding end(actual) value in "H",
THEN change formatting of cells in that row

Does this make sense?
Can someone help out?

Thanks,
mike

**FDibbins** · 10-22-2012, 09:29 PM

please upload a sample file, showing your data, exactly what your expected outcome is, and how you arrived at that
To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**tek_9** · 10-22-2012, 11:25 PM

i made a sheet with concrete example.
the first work item, "make button" (id 1) cannot be started till "get button specs" is finished (id 6)- i.e. it has a value in the end (actual) column.

Im using conditional formatting to show tasks that are blocked. (have any value in blocked by column).

specifically, work item id 1 is blocked by "6". I would like it to look for work item 6 and see if it has a value in end date (actual). if it does, i would like it to change from the pink itallics to regular black text, non italic like the others.

hope this helps,
tek9.xlsx
thanks!
mike

**Marcol** · 10-27-2012, 11:24 AM

Not sure if this is what you need

Select A6:H6
C/F > Formula is:

Formula:

Please Login or Register  to view this content.

Copy the formatting down using the Format Painter

**tek_9** · 10-29-2012, 02:20 PM

First of all, thank you so much for your reply.

This works great, exactly what I was looking for! U Da man. I need to familiarize myself with the INDEX(MATCH( function.

In your example it changes the color to pink when the blocking task is finished.
I changed it slightly so that it's pink if it has a value in the blocked column, and it's changed to bold black if blocking task is finished.

Great work sir!

**Cutter** · 10-29-2012, 08:40 PM

@ tek_9

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.