Trouble highlighting row data.

[Davio]

TABLE1		J	A	N	U	A	R	Y
	1	2	3	4	5	6	7	8	9	10
TEAM1	X	X	X				X	X
TEAM2			X	X	X	X	X
TEAM3									X	X

<tbody>
</tbody>

TABLE2	TASK	START DATE	END DATE
TEAM1	Y	1jan	3jan
TEAM2	X	3jan	7jan
TEAM3	B	9jan	10jan
TEAM1	X	7jan	8jan

<tbody>
</tbody>

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]

Try this.
1. Clear any previous CF from B5:PK17
2. Select B5:Pk17 and apply the CF shown below.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4		16-Jan-19	17-Jan-19	18-Jan-19	19-Jan-19	20-Jan-19	21-Jan-19	22-Jan-19	23-Jan-19	24-Jan-19	25-Jan-19	26-Jan-19	27-Jan-19	28-Jan-19	29-Jan-19	30-Jan-19	31-Jan-19	01-Feb-19	02-Feb-19	03-Feb-19	04-Feb-19	05-Feb-19
5	TEAM1
6	TEAM2
7	TEAM3
8	TEAM4
9
10
11
12
13
17
18
19	Team Name	START DATE					END DATE
20	TEAM1	16-Jan					23-Jan
21	TEAM2	23-Jan					27-Jan
22	TEAM3	19-Jan					10-Feb
23	TEAM1	27-Jan					28-Jan
24	TEAM4	17-Jan					19-Jan
25	TEAM4	21-Jan					21-Jan
26	TEAM4	30-Jan					3-Feb
27
Tables (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5	1. / Formula is =SUMPRODUCT(--($A$20:$A$33=$A5),--($B$20:$B$33<=B$4),--($G$20:$G$33>=B$4))	Abc

or, as Toadstool mentioned, it can also be done with COUNTIFS

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4		16-Jan-19	17-Jan-19	18-Jan-19	19-Jan-19	20-Jan-19	21-Jan-19	22-Jan-19	23-Jan-19	24-Jan-19	25-Jan-19	26-Jan-19	27-Jan-19	28-Jan-19	29-Jan-19	30-Jan-19	31-Jan-19	01-Feb-19	02-Feb-19	03-Feb-19	04-Feb-19	05-Feb-19
5	TEAM1
6	TEAM2
7	TEAM3
8	TEAM4
9
10
11
12
13
17
18
19	Team Name	START DATE					END DATE
20	TEAM1	16-Jan					23-Jan
21	TEAM2	23-Jan					27-Jan
22	TEAM3	19-Jan					10-Feb
23	TEAM1	27-Jan					28-Jan
24	TEAM4	17-Jan					19-Jan
25	TEAM4	21-Jan					21-Jan
26	TEAM4	30-Jan					3-Feb
27
Tables (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5	1. / Formula is =COUNTIFS($A$20:$A$33,$A5,$B$20:$B$33,"<="&B$4,$G$20:$G$33,">="&B$4)	Abc

 

[Toadstool]

Davio,

Having let @Peter_SSs do all the legwork I wonder if I should swoop in with a slightly different flavor of my original formula?
Edit: Ha! I missed swooping by 2 minutes

If I select B5 to PK17, Conditional Format and the formula
=IF(COUNTIFS($A$19:$A$33,$A5,$B$19:$B$33,"<="&B$4,$G$19:$G$33,">="&B$4) > 0,TRUE)
then it highlights the cells with the chosen format where I show H because I can't display cell Fills in this editor.

	A	B	C	D	E	F	G	H	I
3	TABLE 1
4		15-Jan-19	16-Jan-19	17-Jan-19	18-Jan-19	19-Jan-19	20-Jan-19	21-Jan-19	22-Jan-19
5	TEAM 1		H	H	H		H	H
6	TEAM 2			H	H	H
7	TEAM 3							H
8
18	Team	Start Date					End Date
19	TEAM 1	16 Jan					18 Jan
20	TEAM 2	17 Jan					19 Jan
21	TEAM 3	21 Jan					21 Jan
22	TEAM 1	20 Jan					21 Jan

<tbody>
</tbody>

​

 

[Davio]

Peter and Toadstool,

Both formulas work perfectly. Thank you very much.

 

[Davio]

Would it be possible to add text within the highlighted area using the same formula? The text would be referenced from L17:L33.

 

[Peter_SSs]

Would it be possible to add text within the highlighted area using the same formula? The text would be referenced from L17:L33.

We should have started there as, if doing that, the conditional formatting becomes extremely simple - see below.
Formula in B5 is copied across and down.
Remove the old CF and apply this one.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
4		16-Jan-19	17-Jan-19	18-Jan-19	19-Jan-19	20-Jan-19	21-Jan-19	22-Jan-19	23-Jan-19	24-Jan-19	25-Jan-19	26-Jan-19	27-Jan-19	28-Jan-19	29-Jan-19	30-Jan-19	31-Jan-19	01-Feb-19	02-Feb-19	03-Feb-19	04-Feb-19
5	TEAM1	A	A	A	A	A	A	A	A				D	D
6	TEAM2								B	B	B	B	B
7	TEAM3				C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C
8	TEAM4		E	E	E		F									G	G	G	G	G
9
10
11
12
13
17
18
19	Team Name	START DATE					END DATE
20	TEAM1	16-Jan					23-Jan					A
21	TEAM2	23-Jan					27-Jan					B
22	TEAM3	19-Jan					10-Feb					C
23	TEAM1	27-Jan					28-Jan					D
24	TEAM4	17-Jan					19-Jan					E
25	TEAM4	21-Jan					21-Jan					F
26	TEAM4	30-Jan					3-Feb					G
27
Tables (4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5	1. / Formula is =B5<>""	Abc

 

[Davio]

Awesome Peter. Thank you.

 

[Peter_SSs]

Awesome Peter. Thank you.

You're very welcome.

 

[Davio]

We should have started there as, if doing that, the conditional formatting becomes extremely simple - see below.
Formula in B5 is copied across and down.
Remove the old CF and apply this one.

Tables (4)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

4

16-Jan-19

17-Jan-19

18-Jan-19

19-Jan-19

20-Jan-19

21-Jan-19

22-Jan-19

23-Jan-19

24-Jan-19

25-Jan-19

26-Jan-19

27-Jan-19

28-Jan-19

29-Jan-19

30-Jan-19

31-Jan-19

01-Feb-19

02-Feb-19

03-Feb-19

04-Feb-19

5

TEAM1

A

A

A

A

A

A

A

A

D

D

6

TEAM2

B

B

B

B

B

7

TEAM3

C

C

C

C

C

C

C

C

C

C

C

C

C

C

C

C

C

8

TEAM4

E

E

E

F

G

G

G

G

G

9

10

11

12

13

17

18

19

Team Name

START DATE

END DATE

20

TEAM1

16-Jan

23-Jan

A

21

TEAM2

23-Jan

27-Jan

B

22

TEAM3

19-Jan

10-Feb

C

23

TEAM1

27-Jan

28-Jan

D

24

TEAM4

17-Jan

19-Jan

E

25

TEAM4

21-Jan

21-Jan

F

26

TEAM4

30-Jan

3-Feb

G

27

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:89px;"><col style="width:99px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:84px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:77px;"><col style="width:77px;"><col style="width:77px;"><col style="width:77px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B5 =IFERROR(INDEX($L$20:$L$33,AGGREGATE(15,6,(ROW($L$20:$L$33)-ROW($L$20)+1)/(($A$20:$A$33=$A5)*($B$20:$B$33<=B$4)*($G$20:$G$33>=B$4)),1)),"") <tbody> </tbody>

<tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format B5 1. / Formula is =B5<>"" Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter,

Based on the new formula, is it possible to accomplish the following?

1. Have the text appear across the highlighted cells shown in example B. The "( )" are to simulate the highlighted range
Example A: Shows a letter in each cell of the highlighted range.
A B C D E F G H I
TEAM1 (C C C C C)

Example B: Shows one letter across the highlighted range.
A B C D E F G H I
TEAM1 ( C )


2. Can I assign different colors to each team according to the team number? eg. TEAM1=BLUE, TEAM2=YELLOW, TEAM3=RED?

​

 

[Peter_SSs]

Best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

Based on the new formula, is it possible to accomplish the following?

1. Have the text appear across the highlighted cells shown in example B.

A formula cannot merge cells if that is what you mean. It may be possible to just have the letter appear in a single cell at the centre of the range (or near the centre for ranges with an even number of cells). However, if you do that, the conditional formatting would have to go back to one of the much more complicated versions we looked at before.

2. Can I assign different colors to each team according to the team number?

You could, provided ...
- The team names are already in column A and
- You manually set up a different CF for each row based on that team name in column A.

This scenario, using worksheet formulas and conditional formatting seems not very practical to me. I think the result could be achieved if you were to use vba code instead of worksheet formulas and conditional formatting. Of course you or your other users would have to have macros enabled for that to occur. Or you just stick with one of the results so far.

 

[Davio]

Best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.



A formula cannot merge cells if that is what you mean. It may be possible to just have the letter appear in a single cell at the centre of the range (or near the centre for ranges with an even number of cells). However, if you do that, the conditional formatting would have to go back to one of the much more complicated versions we looked at before.

Which variable in the formula will I need to adjust to have the text show within the highlighted range?


You could, provided ...
- The team names are already in column A and
- You manually set up a different CF for each row based on that team name in column A.

This scenario, using worksheet formulas and conditional formatting seems not very practical to me. I think the result could be achieved if you were to use vba code instead of worksheet formulas and conditional formatting. Of course you or your other users would have to have macros enabled for that to occur. Or you just stick with one of the results so far.

I want to keep this simple and stay away from VBA if possible.