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
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