creeping data range
Is there some clever trick to tell a set of charts, which use different
rows, to now use columns B - M instead of A - L?
Our user tracks some monthly figures that are then charted. This user would
like to be able to tell this set of several charts to now use a different
data range after adding another month's figures, ideally with a single
Greg Stigers, MCSA
remember to vote for the answers you like
Jon Peltier has instructions for dynamic charts:
> Is there some clever trick to tell a set o...how to create a chart from a dynamic table of data?
i need to create a chart from a dynamic table,meaning that the table is
auto-generated from database after clicking a button.This is my first time
using macro in excel, so i'm not really well in it.
my idea right now is using Chart.Add,but then i'm having problem on how to
define the cells selection(i must do "the do..loop statement" rite?)
ur help is very appreciated,tq!
Use the macro recorder as a guide. When I record a macro to create a simple
chart it looks like:
' Macro recorded 7/30/2007 by Jon Peltier
ActiveChart.Ch...Pivot tables remember values that are not available anymore
I would like to ask you is it is possible to reset the fields in a pivot
table to make disapear values that they are not anymore available on the
data form the selection-criteria when a fields is drill down
lets make a simple table like ::
uno dos tres
a q 1
s q 2
d q 3
f q 4
g q 5
a w 6
s w 7
d w 8
f w 9
g w 0
make a pivot table and drilldown "uno" (row fields), drillacross "dos"
(column fields) and "tres" as Data-Items
so in the selection criteria for "uno" ...Pasting cell values doesn't remove the formula
I select all, copy, go to a new sheet, edit, paste special, values..its
pastes different numbers and not the number that I previously had on the
If you select the cell in the original range that looks different after you
paste it, what do you see in that cell?
What do you see in the formula bar for that cell?
Any chance you're copying a range that has hidden rows/columns? Or even pasting
to a range that has hidden rows/columns?
> I select all, copy, go to a new sheet, edit, paste special, values..its
...chart resize macro #2
I often have to put a large number of charts in a report that need to be the
I'd like to create a macro that automatically resizes selected charts rather
than manually resizing each one.
I've tried to record a macro where I select a chart and resize it but the
resizing part doesn't seem to get recorded.
Any idea on how I would do this?
Dim oCht As Chart
Set oCht = ActiveChart
oCht.Parent.Width = 768 '1024*.75=768
oCht.Parent.Height = 576 '768 *.75=576
Works perfectly. Thank...Convert a textbox string value into date
I need to convert a textbox string value in the form of 31-12-09 into a date
in the same format, can any one help please, thank you.
>>I need to convert a textbox string value in the form of 31-12-09 into a
date in the same format,
Converting I understand but 'into a date in the same format' is confusing.
If you format it for display the results is a string. If you are going to
use it in calculati...How to insert a "Target" line into basic bar chart
Am looking for some info on how to insert a "Target" line into a bar chart.
I have a basic chart and would like to put on top of the data a line from
left to right which is the target, the bar data would indicate whether this
was acheived or not.
Am running office 2007.
Any help appreciated.
From Jon Peltier's site
Add a Horizontal Line to a Column or Line Chart: Error Bar Method.
Double click on the resulting error bar, and on the Patterns tab, select a
color and line style for the horizontal line. Here I've chosen a solid red
peltiertech.com/Excel/C...Labels on Bubble Charts
Does anybody have the macro to add labels in Bubble charts? Microsoft
seems to have deleted the note on how to do it (located at
, and I can't figure out how to get this information.
Message posted from http://www.ExcelForum.com/
-- View and post Excel related usenet messages directly from http://www.ExcelForum.com
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
...Graph Question. Values from different pages. Not in same cell ranges.
Hi, I have an excel work book which keeps track of
players averages in a dart league.
Every week gets a different page.
I would like pages for individual players, that will keep
track of only 2 columns on a weekly basis, which I will
use to make a graph. Because the stats are sorted by
high average, the players don't always stay in the same
The long way would be for me to reference the necessary
cells, week to week. I was wondering if there is a
script that can do the following pseudocode. Using C1 in
the current individual player's sheet.
C1 = Where "Week1.Ce...(Stacked) Bar/colum chart
I'm trying to create a chart that will show me the 2 bars sort of on top of
each other, in the same column.
So for example if I have a budget value of £500 and a spend of £300. The
first £300 of the colum is one colour and the remaining other £200 is another
colour. Its all on the one column.
When i use the stacked column chart is does £300 in one colour and then £500
in another ontop of the £300 giving a total height for the colum of £800.
All help is appreciated.
Create yourself a cluster...Charting Multiple Series Data with Dates?
I am plotting 5 data series on the same line chart. Dates are plotted on the x axis and concentrations of chloride on the y axis. The dates are formatted like "5/12/2003" as "general" data in the "alignment" menu of "format". The trick is, each data set does not have the exact same date range. For example, one sampling site may have 10 data points that range from 4/2/1991 to 5/12/2003 where as another sampling site may have 12 data points ranging from 4/30/1991 to 5/1/2003. That is to say that the sampling dates for each sampling site are not ...Pivot table from Multiple Consolidation Ranges
Dear All Excel Experts,
Creating a Pivot Table from 1 data table in 1 worksheet
gives me the Pivot Table the way I want it, meaning as
many Page Fields as I defined them at the top left of the
Pivot Table. These Page Fields represent the column lables
and in the drop-down I can choose the data under that
lable of the respective column.
Now I wish to have precise the same Pivot Table, but this
time from data tables spread over several worksheets,
whereby the format of these data tables are exactly the
same. But now the drop-down of the Page Fields at the top
left of the Pivot Table g...How to anchor data label show value option on Pivot Chart
I have a pivot chart and I like to show the data value above the column, however when I resort the chart with a new query, the data labels disappear and I have to go back through format data series/data labels/show value all over again. How do I get the data labels to stay and update automatically based on the new sort
This is an admitted deficiency in pivot tables and charts. Next time you
have to redo a chart's formatting, record a macra, then play it back
every time after that.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Sol...Crop out extra white space in chart
I've created a stacked bar chart in excel 2007 but there is too much white
space to the right of the chart. I try to click on the surrounding box to try
to crop out the white space but this just squashes the chart and its text.
Can someone tell me if there's a way to crop out the extra white space
without effecting the size and proportions of the chart itself?
Appreciate any help.
Have you tried dragging the right border of the plot area?
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"gwh" <...need to add colored horizontal line on chart to show cutoff level
I would like to add a colored horizintal line to my line chart to show a
certain level that the data points should stay above.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
"CoachZ" <CoachZ@discussions.microsoft.com> wrote in message
>I would like to add a colored horizintal line to my line chart to show a
> certain level that the data points should...the date value of series is incorrectly displayed
I'm drawing a 3d graph with 2 series of data. The graph is displayed as 2
separate rows of bars behind each other. In Excel 2007 the type is Colum in
the first row to the far right.
1. series is a units of work. y -axis displayed in units of Thousands
2. series is a date when the units were done. x -axis.
If I hover over the the date bar in the 2nd bar row I can see all the right
including the right date.
the table is:
50000 10.11.2009 (in fact todate() function
All the dates are displayed as 9.02.1900 if I try to dis...Help! Bullen's Scroll/Zoom chart
I was lucky enough to run across Stephen Bullen's Scroll/Zoom a chart
(found here: http://www.oaltd.co.uk/Excel/FunChrt7.zip) -- it's almost
exactly what I need. However, I'm hoping that someone can help me
modify this chart to really get me where I want to be ...
The charting technique that Stephen has allows users to Zoom/Scroll
through a lengthy set of data -- the data is setup so that one column
contains labels and one contains data, using OFFSET formulas to
control the Zoom/Sroll feature. What I want to do is have SEVERAL
columns make up my horizontal axis labels!
...Help with setting up spreadsheet and charts please
Hi, I would be extremely grateful for some help with my spreadsheet.
I have the following data which I need to use to produce charts for a
hospital report fairly urgently.
The rows are the names of patients. This could be up to 200 for each month,
but this varies from month to month. I will have each month on a different
Column F - Ward
Column G - Hospital
Column K - I have a number 1 in the row if the patient meets the criteria
I need to produce a chart(s) to show and compare the following:
How many patients from each ward met the criteria (as indicated by 1 in
column K) a...Find Values
I have a table named cnom which has fields cname,number and total
I also have a table with 10 (for explaination) fields, p1sX (X being 1-5)
which is a name selected from cname in cnom and
ctl1_diff_x (x again 1-5) which specifies a time but is in text format. This
table also has a date field(key).
I'm trying to create a report that shows the total for each cname in cnom
between two specified dates. I've been trying to 0 total in cnom then add on
the amount in ctl1_diff_x to total in the record where cnom is whatever is
in p1sx. This is really tricky because any name could be ...Excel bounding boxes on chart data sources
Why don't Excel bounding boxes appear on some chart data sources? When I
click on some charts, they appear, while other charts they don't. What
setting controls this?
By "bounding boxes" I assume that you mean the highlight around the source
One reason might be what you are selecting - for example, if you select the
chart gridlines the highlight will not appear.
Another reason - if you select a series on a pivot chart the corresponding
range does not highlight.
Also, if the source range is complex, for example a single series using
non-contiguous...Multiplying values in columns
I want to be able to create totals in one column from entries in another
column. For example if a3=10 a7=8 a11=8. I want the corresponding rows in
column b to be multiplied by 18.75. ie . b3= a3*18.75 b7=a7*18.75
Not every row has an entry.
Paste this formula in B3.
Now copy the A3 Formula and paste it for the remaining cells.
Remember to Click Yes, if this post helps!
> I want to be able to create totals i...SendUsing"configuration value is invalid #5
Now I'm receiving a Compile error sub or function not defined.
Private Sub CommandButton1_Click()
Dim iMsg As Object
Dim iConf As Object
Dim cell As Range
Dim Flds As Variant
Application.ScreenUpdating = False
' Set iConf = CreateObject("CDO.Configuration")
' iConf.Load -1 ' CDO Source Defaults
' Set Flds = iConf.Fields
' With Flds
Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Item("http://sch...replace cell value
how to replace sheet1. cell a1 with shhet2 cell a1 if sheet 2 cell a2
matches sheet1 cell a2
Run this small macro:
If Sheets("Sheet1").Range("A2").Value = Sheets("Sheet2").Range("A2").Value
Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A1").Value
Gary''s Student - gsnu200909
> how to replace sheet1. cell a1 with shhet2 cell a1 if sheet 2 cell a2
> matches sheet1 cell a2
On ...y-axis auto range based on data limits
I have a spread sheet with numerous charts. I need the y axis on the charts
to set the minimum appoximately 20% below the lowest data point and set the
maximum appoximately 20% above the highest data point. The major unit should
be set at appoximately 10% of the entire range of data. The spreadsheets are
templates and will have a wide variety data placed into the cells.
Calculate the appropriate values in some cells. Use this technique to apply
the calculated values to the chart's axis:
Jon Peltier, Mic...Displaying value of a variable on a form
I have a bound form which has several text boxes, each box is for a date.
When the form opens, the Open event (through 5 IF statements) counts &
increments the number of these boxes that have null values. I have a
variable for this count. I would like to display the value of this count on
the form. I have a text box for this. At the end of the event, I set the
value of the text box = to the variable which is keeping count. The event
runs fine but on the form, the text box displays the following:
Can anyone explain why this happens and how to correctly display t...