Help Please - Need Formula for to Link Spreadsheet Data

[kcstier]

Good afternoon,

Please see the attached test spreadsheet. I'm looking how to write the formula in the conditional format rules. In the attached spreadsheet, I have Project Status, Indianapolis Timeline, and T-Matrix Tabs. I have data being pulled from the T-Matrix tab to provide dates on the project status. All the tasks from the project status tab will be associated with each individual timeline tab sheet, as you can see I've created "Indianapolis Timeline" for example. When changing the task status (Indianapolis Timeline Tab, Column D) to Completed, I would like the associated cell on the Project Status Tab to turn Green as well as list the current date. So, by marking Drawing Received, "Completed" in the Indianapolis Tab (column G, Row 3) would turn column G, row 10 (Project Status Tab) Green over any other rules.

I don't know if there's a specific way to apply this to the whole sheet as I create more Timeline Tabs for each project or if I will need to just apply the resulting rule to each row on the Project Status sheet as I create Timelines? Thoughts?

Thanks in advance. Please see the attached test spreadsheet.

Thank you, Kevin.

[Ace_XL]

Use this CF rule -- Format Green

=INDEX(INDIRECT("'"&$B8&" Timeline'!D:D"),MATCH(G$6,INDIRECT("'"&$B8&" Timeline'!C:C"),0))="Completed"

Apply to your $G$8:$AG$68 range like the other rules

help-please-need-formula-for-to-link-spreadsheet-data-test3.xlsx

Note: All the timeline tabs should follow the same naming pattern and all of them should have the Task Name and Status in Col C and Col D respectively i.e. same as in your example

[kcstier]

Thanks for helping, When I entered that into the CF rule and placed it at the top of the rules list, and mark the first 3 statuses completed on the timeline tab, it highlights J65, K65 and L65 for me, green. I applied to whole range also, $G$8:$AG$68. Any suggestions?

Thanks.

[kcstier]

Here's an updated Spreadsheet to show what the formula results in. Please help when you have a moment.

Thanks.
Kevin

[JeteMc]

When I opened the file attached to post #4 the first conditional formatting rule read: =INDEX(INDIRECT("'"&$B1048576&" Timeline'!D:D"),MATCH(G$6,INDIRECT("'"&$B1048576&" Timeline'!C:C"),0))="Completed"
When I changed the formula to read: =INDEX(INDIRECT("'"&$B8&" Timeline'!D:D"),MATCH(G$6,INDIRECT("'"&$B8&" Timeline'!C:C"),0))="Completed" as Ace_XL posted, cells G10:H10 turned blue, which I believe is the desired result.
Let us know if you have any questions.

[kcstier]

I have a few more questions on this sheet.
1. How would I get the Indianapolis Status (Project Status Tab, Column A, Row 10) to turn the same color of the latest column of steps on the sheet? For example, column AD, row 10 is red, so the status would be red. etc.
2. If I added a step column to the matrix tab, how would I get it to auto populate into the correct step spot on the Indianapolis Timeline tab? Currently the tasks on the timeline tab, link by column number to the matrix. Also, how to get it to appear in the correct spot of the project status tab?
3. How can I get the start and end date of the chart on the Indianapolis Timeline tab to auto update the start and end date for the range of the project. Chart dates should auto update to start based on timeline column F, row 3 and end column G, row 29. This may change though if I ever add steps to the matrix tab. (See how the chart dates are off right now? I currently have to go in and manually convert the date into a number and enter it into minimum and maximum value of chart to adjust the date range of the chart)

Please see the updated spreadsheet for testing.

Thanks for the help fellas.
Kevin