Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old June 5th 09, 06:08 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 63
Default data range referrences


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.



  #12   Report Post  
Old June 5th 09, 12:08 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default data range referrences

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




  #13   Report Post  
Old June 5th 09, 01:24 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
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.


  #14   Report Post  
Old June 5th 09, 04:29 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default data range referrences

A pivot chart, eh? They're not so flexible. Keep in mind that it's always
best to share as many details as possible when introducing the post, like
chart type, version number, specific formula that's giving difficulties.

So how are you making a pivot table that gives you a slot for 29 Feb 2009?
Pivot tables don't make up dates. Use the whole dates in the pivot table,
but if you only want to show the day number, use a custom date format that
only shows the day, not month and year.

- Jon
-------
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
...
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
m...
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.




  #15   Report Post  
Old June 6th 09, 03:52 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 63
Default data range referrences

On Fri, 5 Jun 2009 11:29:02 -0400, "Jon Peltier"
wrote:

A pivot chart, eh? They're not so flexible. Keep in mind that it's always
best to share as many details as possible when introducing the post, like
chart type, version number, specific formula that's giving difficulties.

So how are you making a pivot table that gives you a slot for 29 Feb 2009?
Pivot tables don't make up dates. Use the whole dates in the pivot table,
but if you only want to show the day number, use a custom date format that
only shows the day, not month and year.


It has TWO columns at the start that are BOTH date fields, and are both
formatted to show up as day of month, and day of week, so the chart shows
all 31 days in January, and the days of the week associated with those
days. Since it is a blood pressure tracking workbook, info like that is
good to see when the user/patient/doctor wants to examine the data for a
trend or spike, etc.

I am trying to perfect it. It is on the Microsoft Templates page under
community submitted templates. Folks are downloading it, so I want to
get it fixed up. I have since made some changes so it is not formatted
as it is on the MS site currently. That sheet uses a simple number for
the day of the month, so that column was not a date column.

Since I grabbed the sheet from someone else, I was unaware that it was
a pivot chart, as I have only recently become aware of the animal's
existence (thanks). Your help has taught me a lot from what you have
given to what I picked up as I have been doing this exercise in error
abatement. :-)


http://office.microsoft.com/en-us/my...056691033.aspx

http://office.microsoft.com/search/r... T101436151033


  #16   Report Post  
Old June 6th 09, 08:43 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default data range referrences

If this were my project (sorry, I don't have time to make it mine) I think I
would have used an Excel List or Table as the data entry region, kept dates
in one column, and not fed a pivot table with this. I'd build a set of
worksheet controls that would allow the user to select a time range to plot,
on a chart with a date scale on the X axis.

I showed an example that worked like this he

Dynamic Charting By Dates -
http://pubs.logicalexpressions.com/P...cle.asp?ID=246

-------
Just over a week! Don't miss it!

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

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


"Archimedes' Lever" wrote in message
...
On Fri, 5 Jun 2009 11:29:02 -0400, "Jon Peltier"
wrote:

A pivot chart, eh? They're not so flexible. Keep in mind that it's always
best to share as many details as possible when introducing the post, like
chart type, version number, specific formula that's giving difficulties.

So how are you making a pivot table that gives you a slot for 29 Feb 2009?
Pivot tables don't make up dates. Use the whole dates in the pivot table,
but if you only want to show the day number, use a custom date format that
only shows the day, not month and year.


It has TWO columns at the start that are BOTH date fields, and are both
formatted to show up as day of month, and day of week, so the chart shows
all 31 days in January, and the days of the week associated with those
days. Since it is a blood pressure tracking workbook, info like that is
good to see when the user/patient/doctor wants to examine the data for a
trend or spike, etc.

I am trying to perfect it. It is on the Microsoft Templates page under
community submitted templates. Folks are downloading it, so I want to
get it fixed up. I have since made some changes so it is not formatted
as it is on the MS site currently. That sheet uses a simple number for
the day of the month, so that column was not a date column.

Since I grabbed the sheet from someone else, I was unaware that it was
a pivot chart, as I have only recently become aware of the animal's
existence (thanks). Your help has taught me a lot from what you have
given to what I picked up as I have been doing this exercise in error
abatement. :-)


http://office.microsoft.com/en-us/my...056691033.aspx

http://office.microsoft.com/search/r... T101436151033



  #17   Report Post  
Old June 11th 09, 04:44 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 63
Default data range referrences

On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier"
wrote:

If this were my project (sorry, I don't have time to make it mine) I think I
would have used an Excel List or Table as the data entry region, kept dates
in one column, and not fed a pivot table with this. I'd build a set of
worksheet controls that would allow the user to select a time range to plot,
on a chart with a date scale on the X axis.

I showed an example that worked like this he

Dynamic Charting By Dates -
http://pubs.logicalexpressions.com/P...cle.asp?ID=246



I made this:

http://office.microsoft.com/en-us/my...058731033.aspx

There is no Feb 29 handling currently as MS does not accept macro
enabled template submissions.

There are no pivot charts or tables. Feb 29 messes up the charts, even
with the #N/A () in place in the date fields, which are one axis.

So I omit your IF statement test string, and was using a button to hide
or unhide that row in two locations. That actually works, and also
likely with the area chart under discussion elsewhere. That is a good
way to code it, if it works with all the charts. Is MS going to patch
SP2 to solve its problems I have been reading about?

Anyway, take a gander at my workbook, if you get a moment. I think the
macro buttons could be eliminated, and the test and change be automated
in a single macro with no need for user interaction (simply run the test
and hide or unhide the rows).

Thanks again, Jon, for all the help you have given me. My work is
close to completion, and much better than the previous version thanks to
your help, and the exercises it put me through.

'Grade' my spreadsheet if you would. I like feedback. I should make
it so the user can select that chart plot line colors on the first page
as well. That would be cool, as then the user could make easy universal
color changes without the recursive exercise of activating all the
pertinent sheets and setting the colors and saving. Having a choice
dialog would make it point and click.

Anyone that monitors and tracks their blood pressure should give it a
glance as well.
  #18   Report Post  
Old June 11th 09, 02:38 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default data range referrences

"The template you are trying to access was not found."

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

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


"Archimedes' Lever" wrote in message
...
On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier"
wrote:

If this were my project (sorry, I don't have time to make it mine) I think
I
would have used an Excel List or Table as the data entry region, kept
dates
in one column, and not fed a pivot table with this. I'd build a set of
worksheet controls that would allow the user to select a time range to
plot,
on a chart with a date scale on the X axis.

I showed an example that worked like this he

Dynamic Charting By Dates -
http://pubs.logicalexpressions.com/P...cle.asp?ID=246



I made this:

http://office.microsoft.com/en-us/my...058731033.aspx

There is no Feb 29 handling currently as MS does not accept macro
enabled template submissions.

There are no pivot charts or tables. Feb 29 messes up the charts, even
with the #N/A () in place in the date fields, which are one axis.

So I omit your IF statement test string, and was using a button to hide
or unhide that row in two locations. That actually works, and also
likely with the area chart under discussion elsewhere. That is a good
way to code it, if it works with all the charts. Is MS going to patch
SP2 to solve its problems I have been reading about?

Anyway, take a gander at my workbook, if you get a moment. I think the
macro buttons could be eliminated, and the test and change be automated
in a single macro with no need for user interaction (simply run the test
and hide or unhide the rows).

Thanks again, Jon, for all the help you have given me. My work is
close to completion, and much better than the previous version thanks to
your help, and the exercises it put me through.

'Grade' my spreadsheet if you would. I like feedback. I should make
it so the user can select that chart plot line colors on the first page
as well. That would be cool, as then the user could make easy universal
color changes without the recursive exercise of activating all the
pertinent sheets and setting the colors and saving. Having a choice
dialog would make it point and click.

Anyone that monitors and tracks their blood pressure should give it a
glance as well.



  #19   Report Post  
Old June 11th 09, 04:55 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 63
Default data range referrences



Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure
Tracker for entire year".

It is on the first page of the "recently submitted" community
templates. on the templates main page at microsoft.com

http://office.microsoft.com/search/r... 100632981033

or, the main templates page and select "recently submitted" in the left
column:

http://office.microsoft.com/en-us/te...595491033.aspx



On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier"
wrote:

"The template you are trying to access was not found."

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

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


"Archimedes' Lever" wrote in message
.. .
On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier"
wrote:

If this were my project (sorry, I don't have time to make it mine) I think
I
would have used an Excel List or Table as the data entry region, kept
dates
in one column, and not fed a pivot table with this. I'd build a set of
worksheet controls that would allow the user to select a time range to
plot,
on a chart with a date scale on the X axis.

I showed an example that worked like this he

Dynamic Charting By Dates -
http://pubs.logicalexpressions.com/P...cle.asp?ID=246



I made this:

http://office.microsoft.com/en-us/my...058731033.aspx

There is no Feb 29 handling currently as MS does not accept macro
enabled template submissions.

There are no pivot charts or tables. Feb 29 messes up the charts, even
with the #N/A () in place in the date fields, which are one axis.

So I omit your IF statement test string, and was using a button to hide
or unhide that row in two locations. That actually works, and also
likely with the area chart under discussion elsewhere. That is a good
way to code it, if it works with all the charts. Is MS going to patch
SP2 to solve its problems I have been reading about?

Anyway, take a gander at my workbook, if you get a moment. I think the
macro buttons could be eliminated, and the test and change be automated
in a single macro with no need for user interaction (simply run the test
and hide or unhide the rows).

Thanks again, Jon, for all the help you have given me. My work is
close to completion, and much better than the previous version thanks to
your help, and the exercises it put me through.

'Grade' my spreadsheet if you would. I like feedback. I should make
it so the user can select that chart plot line colors on the first page
as well. That would be cool, as then the user could make easy universal
color changes without the recursive exercise of activating all the
pertinent sheets and setting the colors and saving. Having a choice
dialog would make it point and click.

Anyone that monitors and tracks their blood pressure should give it a
glance as well.


  #20   Report Post  
Old June 11th 09, 07:43 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default data range referrences

"The template you are trying to access was not found."

I think the problem is that the MS site "helpfully" checks whether I have
2007 installed on my machine (I do, but on a VM, not on the main machine).
If not, I get an option to download manually, and when I click on that link,
it can't believe I haven't upgraded to the newest, glitteriest spreadsheet
program in history, so it can't bring itself to find the file.

Why they can't just put up a simple freaking link, I don't know. They've
pushed the compatibility pack onto everyone anyway, so why should they care?

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

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



"Archimedes' Lever" wrote in message
...


Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure
Tracker for entire year".

It is on the first page of the "recently submitted" community
templates. on the templates main page at microsoft.com

http://office.microsoft.com/search/r... 100632981033

or, the main templates page and select "recently submitted" in the left
column:

http://office.microsoft.com/en-us/te...595491033.aspx



On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier"
wrote:

"The template you are trying to access was not found."

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

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


"Archimedes' Lever" wrote in message
. ..
On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier"
wrote:

If this were my project (sorry, I don't have time to make it mine) I
think
I
would have used an Excel List or Table as the data entry region, kept
dates
in one column, and not fed a pivot table with this. I'd build a set of
worksheet controls that would allow the user to select a time range to
plot,
on a chart with a date scale on the X axis.

I showed an example that worked like this he

Dynamic Charting By Dates -
http://pubs.logicalexpressions.com/P...cle.asp?ID=246



I made this:

http://office.microsoft.com/en-us/my...058731033.aspx

There is no Feb 29 handling currently as MS does not accept macro
enabled template submissions.

There are no pivot charts or tables. Feb 29 messes up the charts, even
with the #N/A () in place in the date fields, which are one axis.

So I omit your IF statement test string, and was using a button to hide
or unhide that row in two locations. That actually works, and also
likely with the area chart under discussion elsewhere. That is a good
way to code it, if it works with all the charts. Is MS going to patch
SP2 to solve its problems I have been reading about?

Anyway, take a gander at my workbook, if you get a moment. I think the
macro buttons could be eliminated, and the test and change be automated
in a single macro with no need for user interaction (simply run the test
and hide or unhide the rows).

Thanks again, Jon, for all the help you have given me. My work is
close to completion, and much better than the previous version thanks to
your help, and the exercises it put me through.

'Grade' my spreadsheet if you would. I like feedback. I should make
it so the user can select that chart plot line colors on the first page
as well. That would be cool, as then the user could make easy universal
color changes without the recursive exercise of activating all the
pertinent sheets and setting the colors and saving. Having a choice
dialog would make it point and click.

Anyone that monitors and tracks their blood pressure should give it a
glance as well.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cond. Format Data Bars of range based on values of another range alexmo Excel Worksheet Functions 4 January 16th 09 05:03 AM
Spreadsheet summary automatically referrences totals of worksheets Hulqscout Excel Worksheet Functions 2 November 17th 08 06:20 AM
When entering data into a range of cells, select the entire range. Q Excel Discussion (Misc queries) 0 September 26th 07 04:36 AM
How do I link data from a horizontal range to a vertical range? davidge Excel Worksheet Functions 3 May 25th 07 08:06 AM
Couting the number of referrences that... speakers_86 Excel Worksheet Functions 11 July 28th 06 03:52 AM


All times are GMT +1. The time now is 08:05 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017