I am preparing a spreadsheet for use by a colleague and am trying to cut down
on the work they will have to do.

I have a spreadsheet with 52 tabs (representing each week of the year).
Information is entered weekly regarding customer sales and formulas calculate
costs of production and sales vs budget.
Another tab is called €˜graph data. Relevant information from the 52 tabs
is collected on this sheet, some extra calculations are performed and then a
series of line graphs are compiled.

One row of excel, lets say row 6 contains information for the horizontal
axis. So in cell
B6 enter Wk1, C6 enter Wk2, D6 enter Wk3 etc until BA=Wk 52

Row 7 is to contain the data which forms the line on the line graphs.

I want the information for all the data for the 52 weeks in row 7 to be
picked up from elsewhere in the worksheet (ie the data in row 7 will be a
formula, such as B7=Z7, which will be information from the relevant weekly
tab (say Wk1) on which a further calculation has been performed ).

I want to enter the formulas for all 52 weeks €“ this way my colleague will
only have to complete the data on the relevant weekly tab, which will fire
through to the Graph Data tab and eventually to the correct cell on row 7 for
inclusion in the graph.

Problem
When I enter the formulas and build the graph, the line plummets down to a
scale of 0 for weeks for which there is currently no data (because those
weeks have not yet occurred). Is there any way of displaying the graph so
that the 52 weeks appear along the horizontal axis (right from week 1) and
the line on the graph only displays for those weeks for which information
exists (ie the formulas which currently display a value of 0 do not display.

The only way I have found round this so far is to leave my colleague to
enter the formula for the graphs each week; but the correct formula is not
always picked up and I was trying to find a way round this.

Sorry to be so long winded. Help appreciated €“ even if only to tell me to
stop banging my head against a brick wall!