I am applying for a job as an executive assistant and before the interview I have to complete a "test." I am having trouble with one direction. Here is the question:
"10.Apply series name and legend key in data labels to the Star data series. "
I have included what I think is the correct answer, but I am not sure. It is under the column chart sheet.
I would like to display the latest data label value for a data series So if I update my table for period 2 the chart should update to display only this value. Then period 3 and so on Workbook attached
View 3 Replies View RelatedI have one series on my chart set as column type with data value labels. I have another series as line type to create a benchmark line across the chart. The chart is interactive where user can select 1 of 20 units.
On some of the units, the labels are overlapping the line type chart and causing a poor display
I am building automated solutions where graphs source data is based on outcome of formulas. In case of line graphs I use #N/A as result if no data is available or formula results into an error - this way the data point and data label will not be shown in the graph.
However this does not work for bar graphs - with #N/A, #DIV/0, 0 or "" the bar itself is not shown but the data label is shown (as #N/A or 0). How can I set-up my formulas so that if result is 0 or formula is in error that the graph does not display the data label.
Attached excel file shows same data in 2 charts - 1 line chart (=OK) and 1 bar chart (=not ok). The data for chart is pulled from 2 other tabs (week&month) and merged into 1 data source for graphs.
I think that this is probably rather simple, but the answer is eluding me, I cannot find anything that helps online either. I have a set of data that looks similar to the sample below, however it is quite a bit longer. I would like the 'Effect' as the X axis, with the 'Score' as Y. This basically shows three columns, which is fine. However I would also like to use the 'ID' as the label against each plot point and I'd like each plot point to look the same.
The problem I am having is that to get the labels to show, I am having to add each series manually, which is rather time-consuming. Is there a correct (faster) way of doing this as I think I'm missing something obvious!
IDScoreEffect
12.11
21.92
31.33
41.81
51.53
61.72
72.33
81.42
92.21
102.13
112.12.................
I have made charts in VBA charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use plotarea (top, left, width, height) for that. Unfortunately when I set these values they keep chaning, there is some scaling going on in Excel and they won't get the specified size. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned
De data for resizing the new charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
I am trying to find a solution now for 3 days and now I only dream of huge charts (which is not good). So please can someone help me with this? This is the last forum, that I can try, I didn't get reactions from others.
with ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
end with
With ChtNew.Plotarea
.Top = ChtOrig.PlotArea.Top
.Height = ChtOrig.PlotArea.Height
.Width = ChtOrig.PlotArea.Width
.Left = ChtOrig.PlotArea.Left
For i = 1 To ChtOrig.PlotArea.Top........
I have a sheet with about 50-60 charts and I am hoping to have a master list of chart titles on the side, how can I link these titles with the charts themselves?
View 1 Replies View RelatedI have a chart that combines 2 xy plots and a set of stacked columns ( total of 4 series).
I can adjust the series ordering between the 2 xy plots, and between the stacked columns, but not for all 4 at the same time.
I'd like the legend to show up as:
Series1, Series2, Series3, Series4
where 1 and 2 are the xy plots and 3 & 4 are the components of the stacked columns.
It is currently:
Series3, Series4, Series1, Series2
Any idea how to re-order the legend entries?
I have a file in which column a shows product names, and columns b and c contains quantitative data. I want to put this in a bubble graph (column b = y axis) and (column c = x axis) and label the bubble with the product names. I tried to make this graph but either it doesn't show labels ,either it doesn't provide bubbles. How can i label the bubbles? I attached a small sample file.
View 4 Replies View RelatedHow do I name my data series in a chart so that the name consist of both a cellreference and my own text?
Example: "Name" & Sheet1!A1. If the value in A1 is 2 then the series name shold be Name2
i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.
Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub
I have a chart with coloring for specific series sets. When I create a similar chart, can I copy the formatting from one range to the other? Why does it default to certain styles?
View 1 Replies View RelatedI have a combination column and line chart with the line series on the secondary axis. Is there a way that I can tell excel to scale the secondary axis so as the line always appears above the columns? As it is it appears above in most instances but sometimes appears below the columns.
View 2 Replies View RelatedThe charts I received have many Series formulas that reference and external spreadsheet
Is there a way that I can modify them and reference the same sheet name but locally
For your information
When selecting menu Formulas and then "Show Formulas" chart data series do not display
When selecting a Chart the "Show Formula" selection is not available (at least I did not find it)
In addition, when selecting the Chart, Then Design, then "Select Data", it replies in the pop-up with the comment "The data range is too complex to be displayed...."
There are about 40-60 formulas as shown below!!
The sheet name with data is D_Brand_Q
HTML Code:
' here is a sample of the current formula
=SERIES('C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$1,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$E$197:$E$218,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$197:$CC$218,2)
' This is the desired formula
=SERIES(D_Brand_Q!$CC$1,D_Brand_Q!$E$197:$E$218,D_Brand_Q!$CC$197:$CC$218,2)
I have used named ranges to make a chart automatically adjust to a changing list of products in one column, but I do not know how to do this with multiple series. If I want to add products to this table, how can I make my chart dynamically adjust multiple series?
So the chart range is currently E5:H10. But I would need it to go beyond row 10 when I add new items.
Sheet1
*EFGH5*Week 1Week 2Week 36Product 11,234,123 1,258,805 1,283,982 7Product 21,234,223 1,258,907 1,284,086 8Product 33,423,321 3,491,787 3,561,623 9Product 4342,121 348,963 355,943 10Product 52,343,424 2,390,292 2,438,098
Excel tables to the web >> Excel Jeanie HTML 4
I have a xy chart with two sets of data, New and Reference. I want New to be the first entry in the legend and to show up on top when the two lines overlap.
i have a chart on a worksheet, it contains multiple series (3) in which i wish to change the values/ range of each series of the chart through vba. Thus far i have struggled due to the lack of intellisense, in which i tried
Worksheets(1).ChartObjects(2).Chart.Series("Fails").Values = Range("b12:b14")
with "Fails" being the name of that particular series.
Is it possible to independantly change the width of donut chart series? I want the outer ring of the donut to be slimmer than the inner ring.
View 4 Replies View RelatedI just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.
My question is related to plotting time series charts using VBA. I have data in the following format.
04/21/08 9:04:47 4.5055
04/21/08 9:04:48 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:50 4.5055
04/21/08 10:05:00 3.5055
04/21/08 10:05:32 3.5055
04/21/08 10:05:40 3.5055
04/21/08 10:05:48 3.5055
04/21/08 10:05:51 3.5055
04/21/08 11:07:59 5.5055
04/21/08 11:09:00 5.5055
04/21/08 11:10:01 5.5055
04/21/08 11:11:02 5.5055
04/21/08 11:13:08 5.5055
04/21/08 11:14:11 5.5055
04/21/08 11:15:16 5.5055
The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.
Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.
Any way to be able to toggle (checkbox) a particular series in a chart.
I found this old thread that shows a simple method: [URL]
BUT, I do not want the series to appear in the legend if it is not plotted.
I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Replies View RelatedI'm trying to use VBA to automatically change the data displayed in an excel chart. To do this I count the number of items displayed on the chart using Chart.Seriescollection.Count. I then loop over the Chart.Seriescollection(x) and try to change the Chart.Seriescollection(x).Formula string to link to the cells I want.
Problem is that, sometimes, the cells the curve is currently linked to are empty. Such a curve does show up in the Count, but I can't access the .Formula, even though this can be done manually from within Excel.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
The order of entries in my chart legend make no sense to me. Why is it not matching the order of the source data?
View 1 Replies View RelatedI have two userforms with a label which displays CompetitorID. I want to transfer content (displayvalue) from UF1.label to UF2.Label. I know labels don't have a value property but want to simply know if it can be done as presently I'm getting run time error 380, can't set property value.
VB : HeadEntryForm.lbCompID = Me.lbCompID ' trf selected competitor ID to ID field on HeadEntryForm
I have a spreadsheet with a customers information and various parts we make for them. I need to be able to take this information and incorporate it into a label format. I need something quick and easy as there can be 150 parts per customer
Customer: ABCD
Customer PO: 12345
Part Number Quantity
AB 1
** 15
EF 22
GH 14
and I need it to output:
CUST: ABCD PART: AB
PO: 12345 QTY: 1
CUST: ABCD PART: **
PO: 12345 QTY: 15
CUST: ABCD PART: EF
PO: 12345 QTY: 22
CUST: ABCD PART: GH
PO: 12345 QTY: 14
I have a userform in Excel and I would like to have a label calculate from the sum of 3 different labels. I have tried a few ways of which none worked.
This is what I currently have. This returns $0.00 in the label value but does not calculate...
Code:
Public Sub TotalCACost()
If TextBox12.Value > "" Then
Label685.Caption = ""
[Code]....
The reason that I have it as a public sub is that I am calling it to Private Sub extBox12_Change() as well as a couple of other textboxes so that when ever TextBox12 or the other textBoxes gets changed, the value will recalculate. The "other textBoxes" change the values of label443, 444, 445, 385, 386, 387 etc..
I created a userform where if a value from cell x is true then the label caption changes to value in cell z. While everything works fine, the label caption does not seem to appear in my userform until i click on the label. Is there anyway that it can appear automatically once the userform opens?
Also example of my code is:
Private Sub EventDateResult_Click ()
If Range("A5") = "1" Then
Me.EventDateResult.Caption = Range("N4")
End If
End Sub
I'm using the code below to create a pie chart and the code works fine.
[Code] .....
However, I now would like to change this so that I can add the word "FTE" at end of this part of code which creates the 'Value' data label.
[Code] ....
I have a plot that shows the position of a drifting buoy in the ocean. I have created a second series that is just the most recent position of that buoy. Everyday, using VBA, I update the chart 2nd series to point to the location of the new lat and long. For this second series, which is only one point, I have data labels turned on. I replace the data label text with the date and time of that latest position. Here is my problem. I can not find a way to format the date. No matter what I try, it has the format mm/dd/yyyy hh:mm AM/PM. I really just want it to be a 24 hour clock (mm/dd/yy hhmm).
I have tried forcing the format of the variable date_of_latest_pos, I have tried changing the cell format that contains the actual date and time...
ActiveChart.SeriesCollection("Latest Position").ApplyDataLabels AutoText:=True, LegendKey:= False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection("Latest Position").Points(1).DataLabel.Select
Selection.Characters.Text = date_of_latest_pos
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With