Line chart gaps disappear from sheets in Excel 2007

[excelman999]

Hello,
Perhaps you can help me with a chart I’m trying to create; basically I’m trying to transfer the empty cells to another sheet as empty cells without them changing to zeros.

Sheet 1
There is a set of 20 numbers say in cells B3:B22
When you graph them (line graph) there will be empty cells among them empty values will be presented on the graph as gaps, WHICH IS WHAT I WANT. No numbers in cells B6, B9, B14 & B19, numbers in all (B3:B22) the other cells.

Sheet 2
However when transferred to sheet 2 the empty cells turn to zero’s.
In cells B3:B22 I referenced values from Sheet 1 (so ‘Sheet 2! B3 =Sheet1!B3, ‘Sheet 2!B4=Sheet 1!B4, etc)
When you graph values from Sheet 2 and you’ll see that empty cells will no longer be presented as gaps but will have a value of zero – UGLY

I can reference values in a different way like ‘Sheet 2’!B3=IF(‘Sheet 1! B3<>””,‘Sheet 1! B3,NA()) or ‘Sheet 2! B3=IF(‘Sheet 1! B3<>””,‘Sheet 1! B3,#NA) then…
On a graph empty cells won’t be default to zeros BUT there won’t be gaps either!

Now the main point is to create a line chart WITH gaps, without using macros (Boss doesn’t trust macros) which I expected to be easy until I attempted it.

Any help appreciated as I’m going around in circles!
Paul

 

[VoG]

Maybe

=IF(‘Sheet 1! B3<>””,‘Sheet 1! B3,"")

 

[excelman999]

Hi,
Thanks for the reply, unfortunately that formula changes to a zero or if set-up for it (by Excel Options) a blank cell, so instead of a gap in sheet 2 as in sheet 1, it just joins the gap with a zero figure, in other words a massive drop on the line chart to the bottom (zero) and then back up to where the gap restarted.
Regards
Paul