Trouble highlighting row data.

[Davio]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]TABLE1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]TEAM1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]TEAM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]TEAM3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]TABLE2[/TD]
[TD="align: center"]TASK[/TD]
[TD="align: center"]START DATE[/TD]
[TD="align: center"]END DATE[/TD]
[/TR]
[TR]
[TD]TEAM1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1jan[/TD]
[TD="align: center"]3jan[/TD]
[/TR]
[TR]
[TD]TEAM2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]3jan[/TD]
[TD="align: center"]7jan[/TD]
[/TR]
[TR]
[TD]TEAM3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]9jan[/TD]
[TD="align: center"]10jan[/TD]
[/TR]
[TR]
[TD]TEAM1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]7jan[/TD]
[TD="align: center"]8jan[/TD]
[/TR]
</tbody>[/TABLE]

I am having issues with TABLE1 highlighting the start and end dates from TABLE2. My goal is to have the data from TABLE2 visualized in TABLE1 The task data does not factor in to the visualization represented in TABLE1. any help or advice is appreciated.​

Thank you.

 

[Peter_SSs]

If all the dates in both tables are within the same month as per your sample then try this formula, copied across and down.
If the dates can span more than one month then we need more details about the layout as well as sample data and expected results for that situation.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	TABLE1		J	A	N	U	A	R	Y					TABLE2	TASK	START DATE	END DATE
2		1	2	3	4	5	6	7	8	9	10			TEAM1	Y	1-Jan	3-Jan
3	TEAM1	X	X	X				X	X					TEAM2	X	3-Jan	7-Jan
4	TEAM2			X	X	X	X	X						TEAM3	B	9-Jan	10-Jan
5	TEAM3									X	X			TEAM1	X	7-Jan	8-Jan
Tables

 

[Toadstool]

I'll make a couple of assumptions:

1. The days of January in the headings are actually complete dates but formatted with a Custom format of "d".
2. The START DATE and END DATE entries are also complete dates formatted as Custom "dmmm".

Taking care of the absolute/relative addressing so you can copy across and down: =IF(COUNTIFS($A$8:$A$11,$A3,$C$8:$C$11,"<="&B$2,$D$8:$D$11,">="&B$2) > 0,"X","")

	A	B	C	D	E	F	G	H	I	J	K

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]TABLE1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1-Jan[/TD]
[TD="align: center"]2-Jan[/TD]
[TD="align: center"]3-Jan[/TD]
[TD="align: center"]4-Jan[/TD]
[TD="align: center"]5-Jan[/TD]
[TD="align: center"]6-Jan[/TD]
[TD="align: center"]7-Jan[/TD]
[TD="align: center"]8-Jan[/TD]
[TD="align: center"]9-Jan[/TD]
[TD="align: center"]10-Jan[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]TEAM1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]TEAM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]TEAM3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]TABLE2[/TD]
[TD="align: center"]TASK[/TD]
[TD="align: center"]START DATE[/TD]
[TD="align: center"]END DATE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]TEAM1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1Jan[/TD]
[TD="align: center"]3Jan[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]TEAM2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]3Jan[/TD]
[TD="align: center"]7Jan[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]TEAM3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]9Jan[/TD]
[TD="align: center"]10Jan[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]TEAM1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]7Jan[/TD]
[TD="align: center"]8Jan[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3
[/TH]
[TD="align: left"]=IF(COUNTIFS($A$8:$A$11,$A3,$C$8:$C$11,"<="&B$2,$D$8:$D$11,">="&B$2) > 0,"X","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Does this work?

 

[Davio]

Peter,

The calendar covers a 12month period.

TABLE1 (A2:PZ10)
(A2:A10) will pull the team names from TABLE2(A2:A10).
(B2:PZ10) contains 1JAN2019-31DEC2019 and will highlight the range between start and end dates from TABLE2(C17:D28)


TABLE2 (A17:D28)
A17:A28 Contains the team names.
C17:C28 Contains the start dates for each task
D17:D28 contains the end dates for each task.

Thank you.

 

[Davio]

I think the formula below is causing the date ranges to be highlighted on different rows. I see that the formula searches $A$20:$A$33 for a match to $A5 then highlights the cells on the corresponding row. What adjustments are needed to have it highlight all $A5 matches on one row?

=(INDEX($G$20:$G$33,SMALL(IF($A5=$A$20:$A$33,MATCH(ROW($G$20:$G$33),ROW($G$20:$G$33)),""),COUNTIF($A$5:$A5,$A5)))<=B$4)*((INDEX($L$20:$L$33,SMALL(IF($A5=$A$20:$A$33,MATCH(ROW($G$20:$G$33),ROW($G$20:$G$33)),""),COUNTIF($A$5:$A5,$A5)))>=B$4)).

 

[Peter_SSs]

Peter,

The calendar covers a 12month period.

In that case ..

If the dates can span more than one month then we need more details about the layout as well as sample data and expected results for that situation.

 

[Peter_SSs]

Can you also ...

1. Confirm or clarify Toadstools assumptions

I'll make a couple of assumptions:

1. The days of January in the headings are actually complete dates but formatted with a Custom format of "d".
2. The START DATE and END DATE entries are also complete dates formatted as Custom "dmmm".

2. Confirm exactly where on your sheet(s) the two tables are as the formula you asked about in post 5 is clearly referring to different ranges to the ones Toadstool & I used.
BTW, what cell is that formula in & where did it come from? Is it one you have been developing yourself or has it come from another forum suggestion in this or another forum?

 

[Davio]

Can you also ...

1. Confirm or clarify Toadstools assumptions

The months are formatted as such in ROW3: B3:PK3 (JANUARY the cell is merged covering from 16 January-15 February)
The dates are formatted as such in ROW4: B4:PK4 12-MON-19 (dd ddd yy)
The start and end dates are formatted as such: 12-JUN-19 (dd ddd yy)


2. Confirm exactly where on your sheet(s) the two tables are as the formula you asked about in post 5 is clearly referring to different ranges to the ones Toadstool & I used.
BTW, what cell is that formula in & where did it come from? Is it one you have been developing yourself or has it come from another forum suggestion in this or another forum?

TABLE1 spans A3:PK17
ROW3: B3:PK3 are months (January-December; each merged to cover from the 16th of the current month to the 15th of the next month. i.e; 16 January-15 February)
ROW4: B4:PK4 are dates

(dd ddd yy)​

ROWS 5-17: A5:A17 returns the TEAM name listed in A20:A33 of TABLE2. B5:PK17 contains the area to be highlighted based on the start and end dates from B20:G33 from TABLE2.

TABLE2 spans A19:G33 This is where I match each of my teams with their jobs (start-end time). I currently have 5 teams for 12 jobs.
ROW19: Headers A19-team name, B19-start date, G19-end date
ROWS 20-33 A19:A33 contain the team names, B19:B33 contain start dates and G19:G33 contain end dates

The formula:

=(INDEX($G$20:$G$33,SMALL(IF($A5=$A$20:$A$33,MATCH(ROW($G$20:$G$33),ROW($G$20:$G$33)),""),COUNTIF($A$5:$A5,$A5)))<=B$4)*((INDEX($L$20:$L$33,SMALL(IF($A5=$A$20:$A$33,MATCH(ROW($G$20:$G$33),ROW($G$20:$G$33)),""),COUNTIF($A$5:$A5,$A5)))>=B$4)) was copied from another template's conditional format that is applied to B5:PK17. My attempt was to tweak in to highlight all team jobs on one row. Instead, it highlights the team jobs on multiple rows

I want the program to highlight the dates between the start and end date, on one row per team, along the calendar year to show which teams have multiple jobs to avoid overlap.​

I hope this clears things up a bit.​

 

[Peter_SSs]

I hope this clears things up a bit.

It has a bit but not completely.

ROW3: B3:PK3 are months (January-December; each merged to cover from the 16th of the current month to the 15th of the next month. i.e; 16 January-15 February)

This is not clear to me
- Are the months a date (eg 1st of the month) formatted to just show the month or are they just Text?
- Merged. I'm not understanding exactly what you have merged. It would seem to me to be unusual to have January merged over part of January and part of February. Can you give a couple of examples of exactly which cells are merged and exactly what values are in those merged cells?
- If something is merged from January 16 to February 15, what is above January 1-15?


Your thread title mentions highlighting but your first post showed 'X's in the cells. Now you are talking about conditional formatting (highlighting) again. Can you confirm that it is just highlighting with conditional formatting that you want?

What version of Excel are you using?

 

[Davio]

It has a bit but not completely.



This is not clear to me
Are the months a date (eg 1st of the month) formatted to just show the month or are they just Text?
-The months are text only in a merged cell.

Merged. I'm not understanding exactly what you have merged. It would seem to me to be unusual to have January merged over part of January and part of February. Can you give a couple of examples of exactly which cells are merged and exactly what values are in those merged cells?

-There are no values in the merged cells, only the text name of each month. I merged them to cover the reporting period and ease identification.
Example: ROW3 B3:AE3 are merged to appear as a header over the 30 day period (16JAN-15FEB) The dates in this example are abbreviated to "15" but would normally read "15-JAN-19"
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE
ROW 3: J A N U A R Y (No values, only merged text)
ROW 4: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 (Individual dates formatted as "15-JAN-19)

If something is merged from January 16 to February 15, what is above January 1-15?
-My calendar begins at 16 January, which is the beginning of my reporting period

Your thread title mentions highlighting but your first post showed 'X's in the cells. Now you are talking about conditional formatting (highlighting) again. Can you confirm that it is just highlighting with conditional formatting that you want?
-Again, I apologize for the confusion. The "x" are there to illustrate the cells being highlighted. I only want the cells highlighted.

What version of Excel are you using?

-Excel Version 1907 Build (11901.20218)