Excel Chart question/assistance

[GeoCan]

Scenario (and this is the "simplified version."

Sheet 1 has the numerical date to be charted:

A B
1 Field No. 12
2 HEIGHT ppm (NO3-N)
3 17 20000
4 21 9000
5 29 17000
6 31 7100
7 36 3800
8 37 12000
9 39 13000
10 41 10000
11 37 no sample

Information is then referenced in Sheet 2 with this formula
=IF(Sheet1!A11=0,"",Sheet1!A11)

I read somewhere that using NA would solve the problem but so far I haven't been able to get that to work. (I was reading an Excel 97 FAQ I believe.) So I also tried
=IF(Sheet1!A11=0,"NA",IF(ISTEXT(Sheet!A11),"NA",Sheet1!A11))

The Charts are based off of the info in Sheet2 so they can print data and chart together.

Client wants TEXT or Blank not to chart, however, in both cases the chart seems to read the formula and charts a Zero Value.

There are literally hundreds of charts and the issue is only on a few but it is still way too much manual intervention to resolve.

Many thanks in advance.

George

 

[AnotherHiggins]

Use a dynamic named range. faq68-1331 should get you started.

John

Every generalization is false, including this one.

 

[SkipVought]

George,

The only way to do that is to

1) Plot DIRECTLY from the source data -- NOT A REFERENCE ON ANOTHER SHEET.

2) remove no sample -- leave the cell EMPTY

3) change Tools/Options-Chart tab-Plot empty cells as not plotted (leave gaps)

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[AnotherHiggins]

Skip's suggestion will work fine if the size of the table being charted is always the same, but it will ever change it will be worth your time to learn how to chart dynamic ranges (which I mentioned in my last post).

More info on how to do it here.

John

Every generalization is false, including this one.

 

[SkipVought]

John,

I focused on the GAP thing because he was so specific about it.

The other thing that I did not mention is to include a data table in the chart, instead of plotting on sheet2.

There are several questions to ask before trucking off on dynamic ranges, cuz I fear that he might have multiple source data tables on a sheet.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[AnotherHiggins]

Skip,

Fair enough.

GeoCan, I'd listen to SkipVought's advice. He won't steer you wrong.

John

Every generalization is false, including this one.

 

[GeoCan]

Thanks guys,

So far, I haven't convince the client to use the single sheet for chart references.

Basically she has one sheet with for data entry and the next 5 sheets extract the info by company to show a chart for each individual data group. Sheet 2 has 3 charts, Sheet 3 has 18, sheet 4 has 25, sheet 5 has 28 and sheet 6 has another 25.

She has to keep them separate as each sheet represents a different company and none can know the others info.

Since all sheets and charts are already made I'll try to convince her to use paste special/values and delete the data entry chart.

Thanks again for the assistance.

And I'll play with the dynamic ranges for my own knowledge.

George

 

[SkipVought]

OK here's what you can do.

Use MS Query to extract the data for each sheet. It's not a formula, but it's not code either. Use Data/Get External Data/New Query - Excel Files - Your Workbook - the Table on the Source data Sheet. Each Query can be configured to return the intended subset of data. The query Data Dange Parameters can be set to refresh the query on Open.

Then you would have HARD DATA on each sheet to plot.

Refer to my FAQ that John referred to earlier, FAQ68-1331 to learn how to define DYNAMIC DATA RANGES for your chart source data ranges. Actually, each Query Table has a range name that could also be used in the Chart Data Range.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]