From: TomCU on
I have an Excel sheet which produces a table of numbers from 3 user-input
numbers. I also want to plot charts of the columns. For most of the examples
all 20 rows won't have data written to them so only the rows with current
data should be non-blank. My problem is getting the data points which are
blank to not plot as zero.
This works perfectly if the table is filtered but the user should not
be expected to know how to run the advanced filter. I've tried copying a
blank/empty cell but it plots in the table as zero. Setting the cells to ""
does not plot on the table but still plot as 0 on the charts. I've tried NA()
as mentioned here which works great on the charts but plots #N/A in the
table. I've tried coditional formatting with a white font but that apparently
does not extend to error message. I've even tried using a macro that presses
DELETE in one field of my IF statement but that shows up in the table as 0.
If I select the blank cells in the table and press DELETE it does the trick
with the charts but it also clobbers any formulas in the table for the next
pass.
Any help would be appreciated.
From: TomCU on
GOT IT! Instead of doing a conditional format on "cell = blank" I tested for
"cell has error" and the white font cleaned up the #N/A error messages. And
the blank data points did not plot in the graphs. It would be cool if I could
only get the labels on the horizontal axis to only show the non-blank rows
(as when it's filtered) but will do. I can FInally get to my other projects.