Morning people,
3 times a week i am required to compile a report in Excel, not a problem this part i can do. Then on a seperate tab i create a pivot chart which shows all the data within the report condensed by just giving totals. Now comes the tricky part.
The report shows how many of our field based staff did how many jobs within the field. Each member of the field based staff is part of a bigger team. each reigon within the uk has been allocated a colour.
Now the report is produced alphabetically so i have to rearrange some teams on order to get them in the right colour order. once done i fill in the cells of the pivot table in the corresponding colours of the team like below:
<TABLE style="WIDTH: 246pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=328 x:str><COLGROUP><COL style="WIDTH: 246pt; mso-width-source: userset; mso-width-alt: 11995" width=328><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 246pt; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl38 height=17 width=328>SOUTH YORKSHIRE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl39 height=17>EAST YORKSHIRE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl39 height=17>NORTH EAST ENGLAND</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #ff6600; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl40 height=17>CUMBRIA AND SCOTTISH BORDER</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #ff6600; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl40 height=17>MERSEYSIDE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #ff6600; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl40 height=17>EAST LANCS</TD></TR></TBODY></TABLE>
My question is how do i create a chart in Excel and copy over the colors in the bars of the bar chart without having to manually fill in each bar as excel doesnt allow multiple selections of the bars.
3 times a week i am required to compile a report in Excel, not a problem this part i can do. Then on a seperate tab i create a pivot chart which shows all the data within the report condensed by just giving totals. Now comes the tricky part.
The report shows how many of our field based staff did how many jobs within the field. Each member of the field based staff is part of a bigger team. each reigon within the uk has been allocated a colour.
Now the report is produced alphabetically so i have to rearrange some teams on order to get them in the right colour order. once done i fill in the cells of the pivot table in the corresponding colours of the team like below:
<TABLE style="WIDTH: 246pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=328 x:str><COLGROUP><COL style="WIDTH: 246pt; mso-width-source: userset; mso-width-alt: 11995" width=328><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 246pt; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl38 height=17 width=328>SOUTH YORKSHIRE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl39 height=17>EAST YORKSHIRE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl39 height=17>NORTH EAST ENGLAND</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #ff6600; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl40 height=17>CUMBRIA AND SCOTTISH BORDER</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #ff6600; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl40 height=17>MERSEYSIDE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #ff6600; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl40 height=17>EAST LANCS</TD></TR></TBODY></TABLE>
My question is how do i create a chart in Excel and copy over the colors in the bars of the bar chart without having to manually fill in each bar as excel doesnt allow multiple selections of the bars.
