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!