Free Microsoft Excel 2013 Quick Reference

Chart headings Results

I would like to be able to add my new data for 2008 to existing tables and
charts started in 2004. I can add the tables but then can't get into the
charts to change the heading . Also, I am unable to use the redo/undo button.

Appreciate any help you can offer.
Thanks
Gillian

I'm trying to put together a piece of code in Excel, to be called by an
external application to regenerate graphs.

The offending code is this:

ActiveChart.PlotVisibleOnly = True
ActiveChart.DisplayBlanksAs = xlNotPlotted
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic

The process fails when trying to set the scaletype to logarithmic,
claiming that negative or zero values cannot be plotted.

There are no negative or zero values in the data series but there are a
number of blank cells. This works perfectly in Excel 2003 but Excel 2007
throws errors.

This is really doing my head in, so any pointers would be much
appreciated!

--
Confidence is what you have when you don't understand the situation

I need help with inserting dates on the axes. I have dates as my column
headings on my worksheet in row 2. I am trying to create a chart using the
dates and just the data I have entered in a particular row, say row 12. It
works fine if I chose line 3, but I need to create charts using other lines.
Is this possible?

Hi

I'm trying to make a graph in excel 2007, it's for an assignment I am doing and I can't for the life of me remember what the tutor demonstrated. I need to make several graphs/charts showing information and how by making that graph it is easier for the reader, I thought I would make a pie chart showing which sales office is doing the best by highlighting the sales office column and the difference column but obviously that didn't work because in the sales office column there are multiples of the same office so the same office has multiple pieces on the pie.

I've been sitting here trying to figure it out for the last couple of hours and my head is just hurting now, lol. I'm probably missing the most obvious point in the world but I can't think right now so if anyone would be able to point me in the right direction I would be very appreciative.

I am trying to make things simple....and well, it's getting beyond me and complicated! I have been self taught on Excel and still learning, so bear with me.

I am needing to take a simple table (using something off the internet for an example) and use the headings to try and point to a cell. Is this possible.

http://www.zipcodeworld.com/images/ss-zc-basic.jpg

Basically needing to use --> City & 8 and the result be: MARICAO.

That is also needing to be put inside a IF statement with some equations to produce another answer...that I can handle.

THANK YOU!

Hi All,

I'm working on a macro that can save me a lot of time ... nothing new here

I have lots of lose "datasets" files .. these files contain some parameters and lots of x and y coordinates.

Now I wanted this macro to import all data from these files, located in one specific directory. I allready achieved this. The imported data sets are 2 colombs wide and many rows long ( also the amount of row varies from one dataset to another). And between each of these 2 colombs of data there are two colombs spacing.

So the scripts loops for n times, and places all datasets in a sheet called "Datasets". In the mean time a blank chart is generated.
The thing I cant get my head arround is how to use the imported datasets to populate this chart?

I use this in the Loop :

    Sheets(chartName).Select
    ActiveSheet.ChartObjects("chartName").Activate
    ActiveChart.SetSourceData Source:=Sheets("Blad2").Range("A1")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(i + 1).Name = F
    ActiveChart.SeriesCollection(i + 1).XValues = "=Blad2!$D$4:$D$12"
    ActiveChart.SeriesCollection(i + 1).Values = "=Blad2!$E$4:$E$12"
But how can I direct the "XValues" en "Values" to the right data?

I need to select the intire dataset , so maybe with :



Greetings,
This one has really got me scratching my head
The macros below change the formulas in column ranges "required" and
"completed". They work fine when run from a forms menu button on any
worksheet, but when I try to run them from a similar button on a chart sheet
I get the following extraordinary results for the 4 formula variants:
=SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"))
=SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"))
=SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"),UserWeighting)
=SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"),UserWeighting)

Sub ApplyWeights()
With Sheets("Matrix")
.Range("Required").FormulaR1C1 = _
"=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""),UserWeighting)"
.Range("Completed").FormulaR1C1 = _
"=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""),UserWeighting)"
End With
End Sub

Sub RemoveWeights()
With Sheets("Matrix")
.Range("Required").FormulaR1C1 = _
"=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""))"
.Range("Completed").FormulaR1C1 = _
"=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""))"
End With
End Sub

Any advice will be much appreciated
TIA
--
David

I have a series of charts and I only want one of them to appear depending on a set of circumstances in the rest of the worksheet. The main sheet is intended to be a template so when a heading is selected from a drop-down menu certain charts appear. Is there any formula/macro that I could use to make different charts appear based on what is included in the dropdown?

I have gestational diabetes. Because of this I have to go through the torture of pricking my fingers 4 times a day and save the readings of the glucose meter. If that wasn't bad enough, the nurse that I have to email once a week wants the data sent in a specific format. I need to make a table that looks like this...

date bef bkfat aftB aft L aft supper
date # # # #

I store my glucose readings in an iPod app, which I can then export to a .csv. I have been working in excel trying to get the pivottable to give me the right results, but because my input looks like this....

Type Value Unit Name Event Date Time
BG 108 mg/dL Out Of Bed 5/27/2011
BG 112 mg/dL After Breakfast 5/27/2011
BG 106 mg/dL After Lunch 5/27/2011
BG 100 mg/dL After Dinner 5/27/2011

I keep having problems in that excel does not merge the rows with the same date heading. I'm afraid this is far beyond my ability to program myself. Any suggestions?

I am trying to create individual radar charts for each of the 277 students in my school. My data is set up with each student in a row and three datapoints for each name.

I am trying to create a macro that will create a chart for each student. I need the column headings to serve as category labels on the chart, so the reference to the labels is fixed, while the data will change for each student.

Using the Macro Recorder, I have set up the code for a single graph (see below) and then I nested this in a For To loop but can't figure out how to have the chart select the range of data for each student and then have the range change for the next student. See in particular the fourth line from the bottom ("ActiveChart.SetSourceData...")

1. How do I write the VBA code for the chart to select the student's data? I always want the labels to be in cells A1:E2 (Absolute Reference) but then want the data to be unique for each student (Relative Reference)

Sub test1()
'
' test1 Macro
' Macro recorded 8/25/2010 by bheese
'

'    
For Num = 3 to 7
Range("A1:E2").Select
    ActiveCell.Range("A1:E2,A5:E5").Select
    ActiveCell.Offset(Num, 0).Range("A1").Activate
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
        "Brand - Radar"
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:E2,A5:E5")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
Next Num
End Sub
Any insights would be appreciated

Hey everyone,

So I have this code that scrolls through each column and uses each
column as the y-axis and outputs a different chart with each increment.
I was able to set up the title of each chart so that it shows the
heading of each column as the title, but now I want the tab on the
bottom of the chart to have this same title (which was obtained from
the first cell of each column). This is the code I have for the title
of each graph and it works fine :

ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C" & C
'C refers to the column index
The title is "Protein 1," which is located in Row 1 Column C.

But for some reason when I tried to use this code to change the tab
name of each chart, it didn't work:
ActiveChart.ChartTitle.Parent.Name = "=Sheet1!R1C" & C
Instead of displaying "Protein 1" on the chart tab on the bottom, it
displays: =Sheet1!R1C4
'The C value works and increments, but for some reason it doesn't see
the reference to output the text of the first row, 4th column cell.

Any ideas would be greatly appreciated!

Thanks,
Sareh

Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed up in row
49. I need the totals in row 49 put into a separate chart for calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same thing for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where I have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled to where
I want it for further calculations. Then in the next row below needs to read
"=D49" and then "=E49" and so on, so that all the column totals are now in
one column from top to bottom. How do I get fill down/fill series to add the
column letters? When I use fill down, it starts going C49, then C50, then
C51, which is NOT what i want it to do. I need it to read =C49, the D49, ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will be
using the totals I have moved over, and when I use fill down with it, it adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect I will
have to put a $ in front of the numbers, but I have no idea what to do with
the letters.
Any ideas?

I'd truly appreciate any help in debugging the RunTimeError1004 that crops up
on most of the coding lines in the macro below.
Thanks in advance for your kind help.

Sub ChartInteger()
'This Chart is drawn to show the outcome of inputting 400+ different values
into an active cell in Sheet("Control")
'The active cell can be any one of a 10x12 range where the column headings
are called TradeAttribute
'A separate macro iterates the 400 input values into each cell and writes
them, and the result of applying
'a complex formula, onto another Sheet("Detail")
'The Sheet("Detail") contains 2x400 row columns of outcomes from varying the
values of each Active Cell 400 times
'I want to use the Active Cell is the identifier for each of the 400 row
columns of Chart data in Sheet("Detail")
'RunTime error 1004 appears at virtually every line

RowValue = ActiveCell.Row
ColumnValue = ActiveCell.Column
TradeAttribute = Sheets("Control").Cells(11, ColumnValue)

ActiveSheet.ChartObjects("ChartInteger").Select
Set BeginInput = Sheets("Detail").Range("VarInput").Offset((RowValue - 12) *
500, TradeAttribute)
Set EndInput = Sheets("Detail").Range("VarInput").Offset(495 + ((RowValue -
12) * 500), TradeAttribute)
Set BeginPL = Sheets("Detail").Range("VarInput").Offset(((RowValue - 12) *
500), TradeAttribute + 12)
Set EndPL = Sheets("Detail").Range("VarInput").Offset((495 + (RowValue - 12)
* 500), TradeAttribute + 12)

Set InPutData = Sheets("Detail").Range(Cells(BeginInput, 0), Cells(EndInput,
0))
Set PLData = Sheets("Detail").Range(Cells(BeginPL, 0), Cells(EndPL, 0))

ActiveChart.SeriesCollection(1).Values = "=Detail!InputData"
ActiveChart.SeriesCollection(1).XValues = "=Detail!PLData"
ActiveChart.SeriesCollection(1).Name = "=Detail!C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Control"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TBDRange"
End With

Sheets("Control").Select

End Sub

Hope someone can help, I have attached a portion of my Pivot Chart in Excel 2003 and have been trying to recreate this in 2007 with some difficulty

I am particularly interested in displaying the field list headings (specifically 'Count of Hit or Miss') in the pivot chart in 2007 but cannot make it work

Hope this makes sense and someone may be able to help

(p.s. I know I can manually add axis titles but just wondering why in 2007 the headings are not included automatically - as they were in 2003)

Thanks in advance

phil

Hi Guys,
I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).
The problem that i am facing is, How to put column headings on the x-axis of each chart.
NOTE: Row heading is already appearing on the y-axis of each chart.
I have also attached my excel sheet for better understanding of my problem.....I want to display DP1, DP2, DP3,... so on on the X-axis of each line graph/chart. Here is my code:

Sub LineCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long

Set Ws = ThisWorkbook.Worksheets("Sheet1")

LastRow = Ws.Range("A65536").End(xlUp).Row
For CurrRow = 2 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs.Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlLine
.SeriesCollection.NewSeries

.SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8"
.SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2"
.Location Where:=xlLocationAsObject, Name:=NewWs.Name
End With
Next CurrRow
End Sub

Hi,

I'm hoping for some help.

I have a number of headings (Planning, Design, Build, Test, and Service Transition to Operations). Each of these headings have a duration associated with them based on the start and finish dates.

I would like to represent these as segments within a Pie Chart as labels within their segment.

The attached file shows the dataset and 'almost' the desired result (Pie Chart). I have labelled how I want the headings to appear, but can't figure out how to achieve this.

Any ideas would be most appreciated.

Thanks, Rai

Hello all. Now that the Fiscal Year is coming to an end, I need a way to select a range of months/years, pull the matching products within those months/years from 2 worksheets, then display the individual combined product totals within each month. In the attached workbook, the Charts sheet shows a chart similar to what I need. Instead of listing the product numerous times, I need the product to be listed one time for each month (if it was sold within the applicable month) with the total sold during the month charted. If 5 products were sold in Jan 2009, then the chart would have the 5 products grouped within the Jan 2009 heading with the name of each product listed and the total of each product charted.

I need to pull this from both the Distribution and Closed_Requests sheets.

This is the first of 2 charts I am working on and hopefully I can take most if not all of this and apply it to my second charting challenge.

I will continue to look through the forum but so far I have not had much success.

Thanks in advance for your help,
Andrew

I am making a report that is get its data from workbook. Now the report is
already made i.e. the charts and tables ar done but the input data is old.
Therefore I have a macro that searches the new info and (tries to) update the
charts and tables. I have a problem though with getting the charts to work.
The code works like this:
1) I specifiy certain headings to search for and stores the address of the
these cells. and then use these cells as point of reference to get the
indata. Code:

If index = 0 Then
varWorksheetInfoArray(0) = "Data"
varWorksheetInfoArray(1) = "Value"
varWorksheetInfoArray(2) = "Exchange rate"
varWorksheetInfoArray(3) = "Inflation"
end if
Call chartMaker1(varWorksheetInfoArray)

I then call a sub that is to replace to old values in the charts with the
new ones.

Private Sub chartMaker1(ByRef varWorksheetInfoArray() As Variant)
Dim i, j As Integer
Dim rng1, rng2, rng3, rng4 As Range

In here I look for the addresses that I need (all these things work..)
If IsEmpty(varWorksheetInfoArray(0)) = False Then
Set rng1 =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(1),
LookIn:=xlValues)
etc..

I then iterate my way through the data to get the correct range (that also
works). Neverthe i and j that are coming they work and specifies the
addresses that sets the range.

Now here comes my problem. I try to get new info to the charts by writing:

Sheets("Rapport").Select
ActiveSheet.ChartObjects("Diagram 2").Activate
With ActiveChart
.SeriesCollection(1).Values = Range(rng2.Offset(i, 0).Address & ":"
& rng2.Offset(j, 0).Address)
.SeriesCollection(2).Values = Range(rng3.Offset(i, 0).Address & ":"
& rng3.Offset(j, 0).Address)
.SeriesCollection(1).XValues =
Sheets(varWorksheetInfoArray(0)).Range(rng1.Offset(i, 0).Address,
rng1.Offset(j, 0).Address)
End With

and it just does not work. All addresses etc. ar correct, it is just the
syntax how to get the new info to the charts that is not working..Please I
would be very thankful if anyone oculd help me!! Thanks!

Hey guys

pls see attached -

The values in B6:F7 of this tab pertain to the matrix in the Communications tab. B13:E18

I would like to highlight the traffic lights above depending on whether or not the results in D14 of the communications tab meet the benchmark set in E14:E17.

Meeting or exceeding b'mark would be green, 5% under Amber 6% under Red.

Ive seen Andy Popes traffic light chart sheet but its whoosh over my head!

Maybe this is too difficult in which case could someone recommend an alternative?

Cheers

I have reached the capacity of my capabilities and need the assistance with creating an Excel Chart.

I have a table of test data with the following five columns of data: HEADING (Value 1, ....)

"Data point labels" (A, B, C, D, E)
"Minimum x-value" (30, 20, 29, 32, 28)
"Maximum x-value" (75, 80, 90, 71, 68)
"Median x-value" (52, 48, 45, 55, 59)
"y-value" (1,4,9,16,25)

Data rules

"y-values" will always be between 1 and 25.
"x-values" will always be between 1000 and 4000000 (4 Million)
"Minimum x-value" < "Median x-value" < "Maximum x-value"
"Median x-value" is NOT calculated from the other two x-values"
With real data there will be between 50 and 100 data points.

Need from chart

1. An x-y scatter graph plotting all the data points.
2. x-values are plotted with the median data point marker sitting on a horizontal bar representing Minimum to Maximum.
3. Data points are labelled (using XY labeller)
4. Y-axis labelled as "Risk"
5. X-axis labelled as "Value"
6. X-axis to be logarithmic.

Same thread as raised on Mr. Excel -> http://www.mrexcel.com/forum/showthr...79#post2758679


No luck finding an answer? You could always try Google.