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!

1) replace the empty cells with =NA(); this displays as #N/A which the chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how empty
cells are to be treated
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
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!

Thanks Bernard
My problem is that the cells are not empty - they contain a formula , the
value of which is 0 until the information becomes available. If I delete the
formulas, I am reliant on the user picking up the correct cells from
elsewhere in the worksheet to update the graphs each week - and I am trying
to avoid this as the wrong cells are sometimes picked up.

I do realise that I may be asking the impossible - but thought I'd post
before giving up - as my Excel level is only beginner to moderate.

Liz

"Bernard Liengme" wrote:

1) replace the empty cells with =NA(); this displays as #N/A which the chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how empty
cells are to be treated
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
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!

Just change the formulas
Suppose you have =my-formula
Replay this by =IF(my_test,my_formula,NA())
where my_test is something like B100 or B10<"" .... some way to test if
the formula should be used or if NA() should be used.

Send me a file (remove TRUENORTH. from my email) and I will show you how
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Skibee" wrote in message
...
Thanks Bernard
My problem is that the cells are not empty - they contain a formula , the
value of which is 0 until the information becomes available. If I delete
the
formulas, I am reliant on the user picking up the correct cells from
elsewhere in the worksheet to update the graphs each week - and I am
trying
to avoid this as the wrong cells are sometimes picked up.

I do realise that I may be asking the impossible - but thought I'd post
before giving up - as my Excel level is only beginner to moderate.

Liz

"Bernard Liengme" wrote:

1) replace the empty cells with =NA(); this displays as #N/A which the
chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how
empty
cells are to be treated
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
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!

Bernard - that is so kind of you. Your email does not display in anything I
can see (can't find TRUENORTH. anywhere). Have tried clicking on your http
address but can't find email there either - could you send it through and
then I'll attach the file.
Many, many thanks
Liz

"Bernard Liengme" wrote:

Just change the formulas
Suppose you have =my-formula
Replay this by =IF(my_test,my_formula,NA())
where my_test is something like B100 or B10<"" .... some way to test if
the formula should be used or if NA() should be used.

Send me a file (remove TRUENORTH. from my email) and I will show you how
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Skibee" wrote in message
...
Thanks Bernard
My problem is that the cells are not empty - they contain a formula , the
value of which is 0 until the information becomes available. If I delete
the
formulas, I am reliant on the user picking up the correct cells from
elsewhere in the worksheet to update the graphs each week - and I am
trying
to avoid this as the wrong cells are sometimes picked up.

I do realise that I may be asking the impossible - but thought I'd post
before giving up - as my Excel level is only beginner to moderate.

Liz

"Bernard Liengme" wrote:

1) replace the empty cells with =NA(); this displays as #N/A which the
chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how
empty
cells are to be treated
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
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!

Hi Bernard

I've managed to do it - following your advice and instructions. So, just to
say thank you very much for taking the time to help me and showing me
patience!
I've used the site a couple of times before, but always feel a bit nervous
that a combination of my lack of knowledge and jargon will irritate those
trying to help - thank you for not intimidating me!

Many, many thanks

Liz

"Bernard Liengme" wrote:

Just change the formulas
Suppose you have =my-formula
Replay this by =IF(my_test,my_formula,NA())
where my_test is something like B100 or B10<"" .... some way to test if
the formula should be used or if NA() should be used.

Send me a file (remove TRUENORTH. from my email) and I will show you how
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Skibee" wrote in message
...
Thanks Bernard
My problem is that the cells are not empty - they contain a formula , the
value of which is 0 until the information becomes available. If I delete
the
formulas, I am reliant on the user picking up the correct cells from
elsewhere in the worksheet to update the graphs each week - and I am
trying
to avoid this as the wrong cells are sometimes picked up.

I do realise that I may be asking the impossible - but thought I'd post
before giving up - as my Excel level is only beginner to moderate.

Liz

"Bernard Liengme" wrote:

1) replace the empty cells with =NA(); this displays as #N/A which the
chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how
empty
cells are to be treated
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
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!

You are most welcome;Tkx for feedback
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
Hi Bernard

I've managed to do it - following your advice and instructions. So, just
to
say thank you very much for taking the time to help me and showing me
patience!
I've used the site a couple of times before, but always feel a bit nervous
that a combination of my lack of knowledge and jargon will irritate those
trying to help - thank you for not intimidating me!

Many, many thanks

Liz

"Bernard Liengme" wrote:

Just change the formulas
Suppose you have =my-formula
Replay this by =IF(my_test,my_formula,NA())
where my_test is something like B100 or B10<"" .... some way to test if
the formula should be used or if NA() should be used.

Send me a file (remove TRUENORTH. from my email) and I will show you how
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Skibee" wrote in message
...
Thanks Bernard
My problem is that the cells are not empty - they contain a formula ,
the
value of which is 0 until the information becomes available. If I
delete
the
formulas, I am reliant on the user picking up the correct cells from
elsewhere in the worksheet to update the graphs each week - and I am
trying
to avoid this as the wrong cells are sometimes picked up.

I do realise that I may be asking the impossible - but thought I'd post
before giving up - as my Excel level is only beginner to moderate.

Liz

"Bernard Liengme" wrote:

1) replace the empty cells with =NA(); this displays as #N/A which the
chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how
empty
cells are to be treated
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Skibee" wrote in message
...
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!