Conditional Formatting

[gdrowell]

I have a worksheet that looks for a date and name match and creates a list from the table.

{=IFERROR(INDEX('Driver Vacations'!$F:$F,SMALL(IF('Driver Vacations'!$K$3:$R$307=January!E$4,ROW('Driver Vacations'!$K$3:$R$307)),ROWS(January!E$5:E16))),"")} where $K$3:$R$307 is the table range.

I want to conditional format any match that occurs in $Q$3:$R$307 without disrupting the original formula above

Any help with be greatly appreciated. Thank you in advance

 

[jasonb75]

You want to format any cell in $Q$3:$R$307 that is equal to January!E$4?

Perhaps a CF rule of =Q3=$E$4

Your question makes no sense in regard to what you want to compare to what for the CF rule to be applied.

 

[MrNJones]

Hi gdrowell,

I'll try and help you. Although I can't fully visualise what your table looks like, I will presume your formula above is contained within the worksheet and not within the Conditional Formatting function.

When using the Conditional Formatting function, it should not interfere with your formulas in the worksheet. Simply select the range $Q$3:$R$307, go to Home > Conditional Formatting > New Rule > Classic > Use a formula to determine which cells to format

Then try the following formula: =IF(Q3<>"",TRUE,FALSE) and select the formatting you require for those cells. Click Ok.

I'm presuming non-blanks are where you have a match. Let me know if that helped and whether I understood your query correctly?

Best wishes,
Nick.

 

[gdrowell]

Thank you both. I will try

 

[gdrowell]

Hopefully, I can try to be more specific in my question.

Please look at the attachment.

If i were to type a date in the table 1 on the left, the formula (at the top) looks for the date to match the date in the header (of table 2 to the right) and inserts the name of the person associated with that date.

How can i format the name (to be italics) from the table 2 to the right, if any a date is typed in the Week 3 column of Table 1?

(I.E. Bill week 3 is 09/01/2018, so in Table 2, his name should appear in Italics, where every one else stays unchanged)

 

[gdrowell]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	All weeks are week ending date
2	Name	No. Of Weeks	Week One	Week Two	Week Three				1/6/2018	3/10/2018	4/21/2018	6/23/2018	7/7/2018	9/1/2018	9/22/2018	10/6/2018
3	Bill	3	1/6/2018	4/21/2018	9/1/2018				Bill	Steven	Bill	Sally	Dana	Bill	Chris	Dana
4	Sally	2	6/23/2018	9/1/2018					Pam		Dana	Chris		Sally	Doug
5	Pam	1	1/6/2018						Chris		Doug			Steven
6	Chris	3	1/6/2018	6/23/2018	9/22/2018
7	Dana	3	4/21/2018	7/7/2018	10/6/2018
8	Doug	2	4/21/2018	9/22/2018
9	Steven	2	3/10/2018	9/1/2018
Sheet1

Cell Formulas
Range	Formula
I3:P9	I3	{=IFERROR(INDEX($A:$A,SMALL(IF($C$3:$E$9=I$2,ROW($C$3:$E$9)),ROWS(I$3:I3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jasonb75]

I'm not seeing where you're specifically requiring week 3, should Sally be in italics for week2, Pam for week 1, etc?

Try applying one of these as a conditional format rule to the range $I$3:$P$9 The bold 3 refers to the week number.

=INDEX($C$3:$E$9,MATCH(I3,$A$3:$A$9,0),3)=I$2

You can specify italic text on the Font tab of the Conditional Formatting > Set Formats box.

As an alternative, looking at all week numbers, see if this is what you need, I may be overthinking it though.
I've used the blue fill to demonstrate because xl2bb wasn't detecting the italic font.

Book1
	I	J	K	L	M	N	O	P
2	01/06/2018	03/10/2018	4/21/2018	6/23/2018	07/07/2018	09/01/2018	9/22/2018	10/06/2018
3	Bill	Steven	Bill	Sally	Dana	Bill	Chris	Dana
4	Pam		Dana	Chris		Sally	Doug
5	Chris		Doug			Steven
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:P9	Expression	=INDEX($C$3:$E$9,MATCH(I3,$A$3:$A$9,0),INDEX($B$3:$B$9,MATCH(I3,$A$3:$A$9,0)))=I$2	text	NO

 

[gdrowell]

Thank you Jason for your response.

How ever, Bill (9/1/18), Chris (9/22/18), and Pam (10/6/18) should be the only CF names because their dates fall under Week 3.

 

[gdrowell]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	All weeks are week ending date
2	Name	No. Of Weeks	Week One	Week Two	Week Three				1/6/2018	3/10/2018	4/21/2018	6/23/2018	7/7/2018	9/1/2018	9/22/2018	10/6/2018
3	Bill	3	1/6/2018	4/21/2018	9/1/2018				Bill	Steven	Bill	Sally	Dana	Bill	Chris	Dana
4	Sally	2	6/23/2018	9/1/2018					Pam		Dana	Chris		Sally	Doug
5	Pam	1	1/6/2018						Chris		Doug			Steven
6	Chris	3	1/6/2018	6/23/2018	9/22/2018
7	Dana	3	4/21/2018	7/7/2018	10/6/2018
8	Doug	2	4/21/2018	9/22/2018
9	Steven	2	3/10/2018	9/1/2018
Sheet1

Cell Formulas
Range	Formula
I3:P9	I3	{=IFERROR(INDEX($A:$A,SMALL(IF($C$3:$E$9=I$2,ROW($C$3:$E$9)),ROWS(I$3:I3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[gdrowell]

Now you can see the headers properly

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	All weeks are week ending date
2	Name	No. Of Weeks	Week One	Week Two	Week Three				1/6/2018	3/10/2018	4/21/2018	6/23/2018	7/7/2018	9/1/2018	9/22/2018	10/6/2018
3	Bill	3	1/6/2018	4/21/2018	9/1/2018				Bill	Steven	Bill	Sally	Dana	Bill	Chris	Dana
4	Sally	2	6/23/2018	9/1/2018					Pam		Dana	Chris		Sally	Doug
5	Pam	1	1/6/2018						Chris		Doug			Steven
6	Chris	3	1/6/2018	6/23/2018	9/22/2018
7	Dana	3	4/21/2018	7/7/2018	10/6/2018
8	Doug	2	4/21/2018	9/22/2018
9	Steven	2	3/10/2018	9/1/2018
Sheet1

Cell Formulas
Range	Formula
I3:P9	I3	{=IFERROR(INDEX($A:$A,SMALL(IF($C$3:$E$9=I$2,ROW($C$3:$E$9)),ROWS(I$3:I3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.