What is the best way to calculate/generate this lot please

[Ric]

Hello all,

I have created a spreadsheet with 4 data sheets
(spend/estimate/RPS/Directive). Spend gets a new column added every month
and the others are updated as and when necessary. Each sheet has various
information on with the data (in hours) profiled from Jan 2003 - Dec 2011.
Column A on each of the data pages is a concatenated string of Contract
Number/Major Task/etc..

There are currently 25 Contract Numbers and upto 6 Major Tasks per Contract.
Currently I have set-up "sheet 1" with a column that contains the 4 sets of
data (D25 = spend/estimate/RPS/Directive) then a row that goes from Jan
03 - Dec 11 (E1H1). So within the cell E2 I have the formula =SUMIF('Spend
Hrs'!$A261:$A505,"*39h100*",'Spend Hrs'!O$261:O$505)(second part of formula
looks down the column and adds the data together where it finds the contract
number in column A) with D2H2
having "+ Previous cell" in F2 to give me cumulative. That is then repeated
on D3
through D5 but looking at the other data sheets. I have then set up in the
same format each major task for that contract by again looking at column A
and pulling out Contract & Major Task (*39h100180*) and adding the month in
the second part of the formula. The RPS line needs to be current months
spend plus remaining months RPS which I currently have to adjust manually

"Sheet 2" is set up in a slightly different format but with the same amount
of data required for equivalent manpower. I have used the same formula as
above but added "/137.5" (which is the current average working month per
person) instead of adding the previous cell.

All this data (Sheet 1 and Sheet 2) is then used to produce graphs. I have
written a simple VB script from a macro that generates the graph from a
specified range, adds a tittle, makes a .GIF of the graph and then deletes
the graph from excel (I found 30 graphs was the max I could have before
getting out of memory errors). Here is the code I use to generate the graph;

Sub doit()
Call W075_Spend("D1:AN5", "W075 Short Term
Overall","Spend\spend_gifs\overall\overallspend.GIF")
End Sub

Sub W075_Spend(therange, thetitle, Fname)
'
' W075_Spend Macro
' Macro recorded 30/03/04 by Ric'

'
Application.ScreenUpdating = False
Range(therange).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(therange),
PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 33
.Fill.BackColor.SchemeColor = 34
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.ColorIndex = 54
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 54
.MarkerStyle = xlX
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
ActiveChart.ChartArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = thetitle
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
End With
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
Application.DisplayAlerts = False
ActiveChart.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End Sub


Now not all contracts have all Major Tasks booking against them. Currently I
am producing 7 graphs per contract but there are quite a few graphs that are
empty (as they are not scheduled to work on that contract but there may be a
stray booking by them that needs to be picked up).

Can anyone suggest a faster/better way to get the data from the 4 sheets so
nothing is missed? as currently I believe I am missing some things in it's
current format. I'd like it to generate a page (Sheet 1) that matches
Contract numbers across the 4 data sheets and displays the information by
line items along the timeline entered so it isn't calculating a lot of zeros
for where data is missing thus speeding up the calculation time and graph
generation stage. And how could i then get it to print out graphs for the
data generated without having to specify a range manualy for each set of
data generated.

Is this possible in Excel or will i have to export to an Access Database and
get it to generate the information and then import it back into excel?

Hope people can understand my ramblings.

Regards

Ric