Create a chart with blank column & cells

**Philb1** · 09-12-2011, 04:32 AM

Hello. I'm new to Excel (OOO Calc user) & am having trouble creating a dynamic chart. I've got the formula sorted. It's the chart. I want to create & format a chart, then leave it blank. I'm doing a spreadsheet for a friend who has Excel & as I've never used Excel before, it's proving to be a challenge.
In Calc, you can do the above with no problem. Can you in Excel?
I've attached a file with some sample cells filed in.
Thanks

**Andy Pope** · 09-12-2011, 04:48 AM

It's not clear to me from your example file what the chart should look like, how the data would relate to a chart or what part of the data is dynamic.

Here is some information on dynamic named ranges which can be used for charting.
http://peltiertech.com/Excel/Charts/Dynamics.html

If you want to have empty information as a starting point then maybe just using a Table linked to a chart will suffice.

**Philb1** · 09-12-2011, 05:16 AM

Hello Andy. Thanks for the reply. I found out what the problem is. Just don't know how to fix it. I'm trying to create the chart from the cell results of formulas that refer to different sheets. If I create a chart from numbers entered in blank cells, it's ok. Do the same from results cells & you get one big column. When looking at the highlighting of the data selection, the data says one thing & the selection boxes say another. The data selection refers to the right sheet & cell references

**Andy Pope** · 09-12-2011, 05:35 AM

If the chart is not picking up the data as you expect then the data is probably not formatted in a way that will allow excel to auto detect. If the result of the formulas is text then excel will assume these are labels.

As I said you attachment is not helping to clarify your problem, attempted solution or desired outcome.

**Philb1** · 09-12-2011, 05:44 AM

I've attached the same file but modified it to show the same results as I'm getting on the other workbook
Thanks

**Andy Pope** · 09-12-2011, 05:53 AM

That's better.

As I said excel is using the returned textual values as text.

See revised formula and chart

**Philb1** · 09-12-2011, 05:53 AM

I've remembered what it is now. Excel doesn't like "" as a cell result. It wants #N/A
Thanks for trying. It nudged me into remembering. Trouble is it looks *** ugly with #N/A all over the place. If you conditional format to get rid of the error, it shows when the area is highlighted for charting. Doesn't matter with the dynamic chart. It does for the other charts the user will be creating. Any ideas on that?
Cheers

**Andy Pope** · 09-12-2011, 06:00 AM

I don't see the #N/A when the cells are selected once CF is applied.

You can always create 2 tables. One for the chart the other for the user.

**Philb1** · 09-12-2011, 06:11 AM

Either way the data for the chart will be coming from another sheet. I've got all the sorting, date & indexing formulas hidden on a helper. I want to keep it all out of his sight. If he can see it, he'll want to play. What he doesn't know won't hurt him, if you know what I mean

He'll have to put up with the N/A's.
On the last file you sent, it shows the #N/A's when the area is selected. That's the bit I was trying to hide

**Andy Pope** · 09-12-2011, 06:12 AM

If you set the chart, via the Select Data dialog, to display hidden cells you can simply hide the cells.

**Philb1** · 09-12-2011, 06:30 AM

I had a play with that. It hides the empty cells on the chart, but doesn't stop the #N/A from showing when you highlight the cells before the chart is created.
It's obviously something that's different in Excel & I've spent an eternity trying to overcome it lol. He'll have to put up with it
Thanks for your help
Cheers
Phil