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

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.