Hi folks, long time lurker and this forum has been a great help, but now I'm stuck, I'm signed up and hoping you can help with what should (in my head) be as easy as pie, but I'm stumped
I have a basic 'resource required' table that tells me what various resource I need and for how long (from/to). The old version was to have week dates put in the top row, resource types duplicated in column A and fill it out cross tab style. This was ok but with 0000's of records it's a bit unweildy!
So, my data looks like this:
I have a basic 'resource required' table that tells me what various resource I need and for how long (from/to). The old version was to have week dates put in the top row, resource types duplicated in column A and fill it out cross tab style. This was ok but with 0000's of records it's a bit unweildy!
So, my data looks like this:
<TABLE class=MsoNormalTable style="WIDTH: 496pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 0cm 0cm 0cm" cellSpacing=0 cellPadding=0 width=661 border=0><TBODY><TR style="HEIGHT: 31.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 31.5pt; mso-border-alt: solid black .5pt" vAlign=bottom noWrap width=64>
ID<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 156pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 31.5pt; mso-border-top-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom noWrap width=208>
Project<o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 144pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 31.5pt; mso-border-top-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom noWrap width=192>
Start<o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 34pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 31.5pt; mso-border-top-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom noWrap width=45>
Role<o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 31.5pt; mso-border-top-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom noWrap width=75>
End<o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 31.5pt; mso-border-top-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom noWrap width=77>
Availability<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: silver 1pt solid; WIDTH: 48pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid silver .5pt; mso-border-top-alt: solid silver .5pt" vAlign=bottom width=64>21<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 156pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid silver .5pt; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=208>Project 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 144pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid silver .5pt; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=192>01/01/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 34pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid silver .5pt; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=45>Role 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid silver .5pt; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=75>01/04/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid silver .5pt; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=77>
1<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: silver 1pt solid; WIDTH: 48pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt; mso-border-left-alt: solid silver .5pt" vAlign=bottom width=64>
22<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 156pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=208>Project 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 144pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=192>01/02/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 34pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=45>Role 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=75>01/04/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=77>
1<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: silver 1pt solid; WIDTH: 48pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt; mso-border-left-alt: solid silver .5pt" vAlign=bottom width=64>
23<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 156pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=208>Project 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 144pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=192>01/03/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 34pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=45>Role 2<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=75>01/04/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=77>
0.5<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: silver 1pt solid; WIDTH: 48pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt; mso-border-left-alt: solid silver .5pt" vAlign=bottom width=64>
24<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 156pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=208>Project 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 144pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=192>01/01/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 34pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=45>Role 2<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=75>01/05/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=77>
0.5<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: silver 1pt solid; WIDTH: 48pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt; mso-border-left-alt: solid silver .5pt" vAlign=bottom width=64>
26<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 156pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=208>Project 1<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 144pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=192>01/01/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 34pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=45>Role 3<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=75>01/06/2012<o></o>
</TD><TD style="BORDER-RIGHT: silver 1pt solid; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid silver .5pt; mso-border-right-alt: solid silver .5pt" vAlign=bottom width=77>
1<o></o>
</TD></TR></TBODY></TABLE>
Now, where I am stumped is I need to make a line chart (boss's request) showing the Availability required over time but as I have to/from dates and not the individual dates I can't seem to get something that resembles what the real picture is
For example, I can set graphs that move with the dates but I need it so that if I was to go along the axis and pick a date not in the data directly, but between two dates I can see how much resource is needed for a given date.
it seems so simple in my head but I just can't work it out... if anyone can understand my garbled plea I would be most appreciative of your help!