View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.charting
Archimedes' Lever Archimedes' Lever is offline
external usenet poster
 
Posts: 63
Default data range referrences

On Fri, 5 Jun 2009 07:08:23 -0400, "Jon Peltier"
wrote:

I tested before I posted before, and I've just tested again. If the last
cell has #N/A in it, the chart doesn't even include a blank slot for the
date. This is tested on a line chart and a column chart, both having
date-scale axes. In a line chart, there will be no space even with a
category (text label) axis if you also have a condition on the X value that
returns NA() when the date is #N/A. A column chart with a category (text
label) axis will have a slot with #N/A as the label.

- Jon
-------


Mine adds the slot for years with a leap year, but when it inserts #N/A
as it does correctly in the first two columns of my data range, the chart
blanks the slot labels, but the slot remains.

Hiding and unhiding the row most definitely works.

Perhaps my improper function is related to the chart type, as in the
chart is a pivot chart.


Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html

Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"Archimedes' Lever" wrote in message
.. .

Well, it does place the NA in the date cell just fine, and the chart
does not put those tags in, but it does still make a 29 day chart, and
despite telling it to ignore the data, if there is data in the data
cells, it appears in the non-labeled 29th day in the chart.

What I would need to do is actually delete the whole line in the data
set, because the chart is pivot based, and the table adds a line and
incorporates it into the chart automatically when data is added to the
row just under the last row.fine. It also concatenates the chart size
when said row is removed. I just found out that this all works fine by
merely hiding the row, so my test needs to trigger a hide or unhide event
at a row location. The chart follows this move whether there is data in
the hidden row or not.

So it is pretty close. I think I could do this now with a macro
Since I know how to do the test, and I know how to hide and unhide via
menu, and I recall seeing code for it (hiding) while in the help file
too.

Is there a straight excel function that would hide or unhide a row, or
will VB be the only way?

Thanks for the test string. That ties it all together. I know it is
simple, but I do not know all the command functions, so I could not
consider even attempting the test string. When I attempted the hide
while I authored this post, I found that the chart follows the hide
event.

Thanks again. Pretty close to a dynamic February data table and
tracking charts. Only a few decisions left to make.

My almost ready chart has been DL'd on MS' site 100 times. :-)

After this fix, it will be as good as it gets... or as good as it
needs to be anyway.


On Wed, 3 Jun 2009 08:16:48 -0400, "Jon Peltier"
wrote:

All true. I tested it before posting to reassure myself that my memory was
correct and to help with the description.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Only two more weeks!

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Archimedes' Lever" wrote in message
...
On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier"
wrote:

Make your life simple. Suppose your dates are in column A, from A2 (Feb
1)
to A30 (Feb 29). Put this formula into cell A30:

=IF(MONTH(A29+1)=2,A29+1,NA())

This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The
#N/A
will not be plotted in an XY chart or a line chart, and the data will
progress in one step from 2/28 to 3/1 on non-leap years.

- Jon

That 'IF(MONTH' test segment is what I was looking for. My third month
is on a separate sheet and will always read as the first day of the
third
month on the first row. I am doing this for Feb only and this will
allow
me to selectively fill both the date column and the day of week column,
which also shows up in the chart.

So this will make the 29 row blank (#N/A) on other than leap year, and
that blank row will not get used on the chart sheet, even though it is
based on the 29 row range? That is what I gathered from the letter part
of you response, and this will even keep that row out of the 365 day
years.? I think this may be exactly what I was after. This switch, in
fact is short and sweet, whereas I was trying to switch entire charts or
worksheets on which that given month would be based on.

I will let you know. Thank you.