How to Format Values on a Cell - Probably Requiring a VLOOKUP

[PaulFerris]

Thanks to the awesome help by Eric (https://www.mrexcel.com/forum/excel-questions/992898-help-trying-build-roadmap-excel.html) I have a functioning roadmap displaying my projects.

Now my boss has asked that we applying some additional conditional formatting so that we can show projects that have training.

I have included a couple of new columns in my export on the 'Data' sheet

[TABLE="width: 799"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Launch Week[/TD]
[TD]Portfolio Group[/TD]
[TD]Name[/TD]
[TD]GTM ID[/TD]
[TD]Capability Requirements[/TD]
[TD]Endorsement Status[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]CC[/TD]
[TD]Project Name 1[/TD]
[TD]3643[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]3-Apr-17[/TD]
[TD]CC[/TD]
[TD]Project Name 2[/TD]
[TD]5531[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Mar-17[/TD]
[TD]CC[/TD]
[TD]Project Name 3[/TD]
[TD]8632[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]Risk & Compliance[/TD]
[TD]Project Name 4[/TD]
[TD]9233[/TD]
[TD]-[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]CC[/TD]
[TD]Project Name 5[/TD]
[TD]9451[/TD]
[TD]-[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Mar-17[/TD]
[TD]CW[/TD]
[TD]Project Name 6[/TD]
[TD]9571[/TD]
[TD]-[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]20-Mar-17[/TD]
[TD]Digital[/TD]
[TD]Project Name 7[/TD]
[TD]9651[/TD]
[TD]Knowledge Management[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]CC[/TD]
[TD]Project Name 9[/TD]
[TD]10451[/TD]
[TD]-[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Mar-17[/TD]
[TD]CC[/TD]
[TD]Project Name 10[/TD]
[TD]10473[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]CC[/TD]
[TD]Project Name 11[/TD]
[TD]10491[/TD]
[TD]-[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]3-Apr-17[/TD]
[TD]CC[/TD]
[TD]Project Name 12[/TD]
[TD]10611[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]13-Mar-17[/TD]
[TD]CC[/TD]
[TD]Project Name 13[/TD]
[TD]10991[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]CC[/TD]
[TD]Project Name 14[/TD]
[TD]11173[/TD]
[TD]NA[/TD]
[TD]Endorsed[/TD]
[/TR]
[TR]
[TD]27-Mar-17[/TD]
[TD]TW[/TD]
[TD]Project Name 8[/TD]
[TD]X.010371[/TD]
[TD]Training[/TD]
[TD]Endorsed[/TD]
[/TR]
</tbody>[/TABLE]


At the moment I have a conditional format on the cells in the "Roadmap' sheet to check if the result of the following formula

{=IF(C$4="","",IFERROR(INDEX(Data!$C$2:$C$500,SMALL(IF((Data!$A$2:$A$500=C$4)*(Data!$B$2:$B$500=$A$15),ROW($A$2:$A$500)-ROW($A$2)+1),ROWS(C$15:C15))),""))}

If it isnt NULL then color the cell and place a black dotted border around it.

Now I need another conditional formatting that does the following

• Check the name of the project in the current cell in the 'Roadmap' sheet (eg. C15)
• Find that value in column C of the 'Data' sheet
• Check to see if column E for this value has 'Training' entered.
• If it does, apply a new conditional format (eg. a red border instead of the current black border) on the cell in the 'Roadmap' sheet
• Check the next cell on the 'Roadmap' sheet

How would I add another conditional format that checks for the value displayed in a cell on the 'Roadmap' sheet, then look for that value in the 'Data' sheet and check if it has Training entered in column E of the 'Data' sheet

Any ideas?

 

[FDibbins]

If this is your CF rule....

{=IF(C$4="","",IFERROR(INDEX(Data!$C$2:$C$500,SMALL(IF((Data!$A$2:$A$500=C$4)*(Data!$B$2:$B$500=$A$15),ROW($A$2:$A$500)-ROW($A$2)+1),ROWS(C$15:C15))),""))}

you can probably shorten that to just...

=INDEX(Data!$C$2:$C$500,SMALL(IF((Data!$A$2:$A$500=C$4)*(Data!$B$2:$B$500=$A$15),ROW($A$2:$A$500)-ROW($A$2)+1),ROWS(C$15:C15)))

no ARRAY, CF will convert it to ARRAY anyway
Working on the other question

 

[PaulFerris]

Thanks

 

[PaulFerris]

OK,

I think I may have worked it out

I used the rule =INDEX(Data!$G:$G,MATCH(C15,Data!$C:$C,0))="Training" and that seems to be working

Hopefully I have that right