Auto-expanding (dynamic?) Chart with linked cells and non empty cells

Hi all

Either this is quite messy or I have reached my comprehension capacity for the evening.

I need to produce a chart showing planned and actual hours worked by week.

I am working with two workbooks. One of which has the 'actual hours worked' entered manually each week. I need to have a worksheet in the second workbook containing a graph showing these actual figures against the planned figures. I would like the chart to auto expand with the actual figures.

Unfortunately, I can only make changes to the worksheet with the graphs on so I cannot alter the layout of the rest of the workbooks.

Here is a description of what I have currently, (IF formulae explained later):

Column(A)
Week numbers starting from A3. (eg. A3=IF(C3="","",ROW()-2))

Column(B)
Planned Hours
These cells refer to a column in a different sheet of same workbook. 52 rows.

Column(C)
Actual Hours
The formulae in this column refer to Column(D). See below for reason*. (ie. =IF(D3=0,"",D3))

Column(D)
These are linked cells to the second workbook where the actual hours are entered. Unfortunately these are entered horizontally so I have to use the following formula in this column: {=TRANSPOSE('[Book2.xls]Sheet1!$C$113:$BC$113)}

*The reason I have Column(C) referring to (D) is probably entirely unnecessary. I thought for the chart to only source the data with an actual value I would have to make the cells empty, hence the if(**=0,"" all over the place! Feel free to tell me to scrap what I have so far as it is the result of going round in circles for an hour or so!


Proof reading the above, makes me think I am not explaining too well but my head is scrambled so please just ask if I need to clarify anything!

N.B. Once I have the chart sourcing the right data and auto-expanding, I will be interested in making it look professional, with variances over the columns etc. That can wait until another day though.

Thanks in advance for any help.

I should note that I am working in Excel 2007 but I need it to be compatible with 2003. (Which is why I have not used the ISBLANK function.)