blank vs. 0 when plotting a dated timescale graph

N

nbayless

Greetings!

I have a plot with dates on the x-axis and numbers on the y-axis.

I am importing database data into excel in the first tab, and
referencing that data in the second tab. The problem I run into is
that when the imported cell is blank, the reference in tab 2 displays
a 0 value. This presents a problem for my plot, which is auto-scaled
on the x-axis.

Example case (3x3 table):
x values: 03/01/07, 05/01/07, blank cell
y values: 50, 30, 20

The blank cell is for a future date that has not transpired. The y-
value is known, but the actual date realized is not, until it happens.

(1) Make a simple column chart, single series, with x values and y
values defined accordingly. You will get an auto-scaled plot showing
March, April, and May, and values of 50 and 30 plotted. This is what
I am looking for, so all is good when the data is manually enterred.

(2) Now, I make the cells in the table reference cells in Tab1 with
the values (e.g. ='Tab1'!A3). The blank cell is blank in tab1, but
displays 0 (or 1/0/1900 if cell is formatted as a date). This now
makes the plot graph a value of 20 on the date of 1/0/1900. This is
not what I'm looking for. However, if I select the cell in Tab2 and
press delete, the plot reverts to the correct view, but I lose my
reference.

I want the behavior in (1) when I'm referencing values in the other
tab, as in (2). Is this possible? Can I use an IF formula that's
something like IF('Tab1'!A3="", [leave blank / set blank], 'Tab1'!
A3)? Is there a setBlank() formula of some kind that can be used in
this fashion, whereby still maintaining the reference to the imported
cell value?

Any insight would be much appreciated.

Thanks,

Nate
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top