Visual a rota in a Gantt chart

[njenkins]

I have a rota with job roles as columns and a dates down the first column and initials in the cells displaying who is in what role that day.

I would like to link this data to a worksheet that has this information as a Gantt chart - with roles down column A and dates long the columns with a bar chart effect (each persons initial as a different colour) blocking out the cells where they are working

 

[Peter_SSs]

I have done these on single sheets but they are adaptable to 2. If you have trouble with that, please advise which of these two (or something else) you actually want.

Apply the Conditional Formatting to H2:Q4

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Job 1	Job 2	Job 3				01-Oct-19	02-Oct-19	03-Oct-19	04-Oct-19	05-Oct-19	06-Oct-19	07-Oct-19	08-Oct-19	09-Oct-19	10-Oct-19
2	01-Oct-19		EF				Job 1
3	02-Oct-19	AB					Job 2
4	03-Oct-19	AB					Job 3
5	04-Oct-19			AB
6	05-Oct-19		AB	CD
7	06-Oct-19	CD	AB
8	07-Oct-19	CD	AB
9	08-Oct-19	CD		EF
10	09-Oct-19	CD		EF
11	10-Oct-19			EF
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	1. / Formula is =INDEX($B$2:$D$11,MATCH(H$1,$A$2:$A$11,0),MATCH($G2,$B$1:$D$1,0))="EF"	Abc
H2	2. / Formula is =INDEX($B$2:$D$11,MATCH(H$1,$A$2:$A$11,0),MATCH($G2,$B$1:$D$1,0))="CD"	Abc
H2	3. / Formula is =INDEX($B$2:$D$11,MATCH(H$1,$A$2:$A$11,0),MATCH($G2,$B$1:$D$1,0))="AB"	Abc

Below, the formula in H2 is copied across and down & the CF applied to H2:Q4

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Job 1	Job 2	Job 3				01-Oct-19	02-Oct-19	03-Oct-19	04-Oct-19	05-Oct-19	06-Oct-19	07-Oct-19	08-Oct-19	09-Oct-19	10-Oct-19
2	01-Oct-19		EF				Job 1		AB	AB			CD	CD	CD	CD
3	02-Oct-19	AB					Job 2	EF				AB	AB	AB
4	03-Oct-19	AB					Job 3				AB	CD			EF	EF	EF
5	04-Oct-19			AB
6	05-Oct-19		AB	CD
7	06-Oct-19	CD	AB
8	07-Oct-19	CD	AB
9	08-Oct-19	CD		EF
10	09-Oct-19	CD		EF
11	10-Oct-19			EF
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	1. / Formula is =H2="EF"	Abc
H2	2. / Formula is =H2="CD"	Abc
H2	3. / Formula is =H2="AB"	Abc

 

[njenkins]

Thank you.
I can do this using a paste special and transposing the data. Is there anyway of linking the two tables so that any change in the vertical data automatically changes in the other?

 

[Peter_SSs]

Is there anyway of linking the two tables so that any change in the vertical data automatically changes in the other?

I assume that you mean the dates and job titles?
If so ..
H1 copied across as far as you might ever need. [I have assumed that the dates in column A are 'real' dates (numbers) not text values]
G2 copied down as far as you might ever need. My layout on the same sheet means that my G2 can only get copied down as far as row 6 but you should be unrestricted if your second table is on another sheet.
H2 (across and down) has also changed a little

Conditional formatting for either option remains the same but is applied to whatever columns and rows are determined by where you copied the headings to.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Job 1	Job 2	Job 3				01-Oct-19	02-Oct-19	03-Oct-19	04-Oct-19	05-Oct-19	06-Oct-19	07-Oct-19	08-Oct-19	09-Oct-19	10-Oct-19
2	01-Oct-19		EF				Job 1		AB	AB			CD	CD	CD	CD
3	02-Oct-19	AB					Job 2	EF				AB	AB	AB
4	03-Oct-19	AB					Job 3				AB	CD			EF	EF	EF
5	04-Oct-19			AB
6	05-Oct-19		AB	CD
7	06-Oct-19	CD	AB
8	07-Oct-19	CD	AB
9	08-Oct-19	CD		EF
10	09-Oct-19	CD		EF
11	10-Oct-19			EF
12
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	1. / Formula is =H2="EF"	Abc
H2	2. / Formula is =H2="CD"	Abc
H2	3. / Formula is =H2="AB"	Abc

 

[njenkins]

apologies.

I meant the data in the cells might change. So people may swap roles on a particular day

 

[Peter_SSs]

apologies.

I meant the data in the cells might change. So people may swap roles on a particular day

In that case there is nothing to do.
The suggestions that I made in post 2 are automatically updated if the data in B2:D11 change. Did you try changing that data?

For example, here I have taken the sheet exactly as posted as my second example in post 2 and moved 'AB' from cells B3:B4 to D3:D4 and 'EF' from C2 to B4 and the new arrangement is automatically shown in columns G:Q

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Job 1	Job 2	Job 3				01-Oct-19	02-Oct-19	03-Oct-19	04-Oct-19	05-Oct-19	06-Oct-19	07-Oct-19	08-Oct-19	09-Oct-19	10-Oct-19
2	01-Oct-19						Job 1			EF			CD	CD	CD	CD
3	02-Oct-19			AB			Job 2					AB	AB	AB
4	03-Oct-19	EF		AB			Job 3		AB	AB	AB	CD			EF	EF	EF
5	04-Oct-19			AB
6	05-Oct-19		AB	CD
7	06-Oct-19	CD	AB
8	07-Oct-19	CD	AB
9	08-Oct-19	CD		EF
10	09-Oct-19	CD		EF
11	10-Oct-19			EF
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	1. / Formula is =H2="EF"	Abc
H2	2. / Formula is =H2="CD"	Abc
H2	3. / Formula is =H2="AB"	Abc