I have read quite a few tutorials but none of them really reference what I am trying to achieve. Essentially I have one column with 2 options: "Compliance" and "Improvement". I am trying to make a dynamic chart based on the number of occurrences. From my understanding you need to use a "Named Range" and "Count" but I can not find any information on how you combine the two to make a chart.
View 3 RepliesI am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I'm having difficulty trying to get the second file appended to the bottom of the 1st imported file. I get "run-time error '13' type mismatch". There is no difference between the two files. I'm thinking there is a problem with my range statement in the second file import, but this range works fine in other macros. Here's my code so far:
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\server1usersmydataIMPORT1.CSV", Destination _
:=Range("$A$1"))
.Name = "IMPORT1"
.FieldNames = True
.RowNumbers = False
[Code] .........
I am trying to make a chart and I just used the macro recording thing to get the code. I have a variable called days.
ActiveChart.SeriesCollection(1).XValues = "='Pair Data'!R2C2:R24C2"
Instead of going to row 24 I want to go to row days+1 so I did the following:
ActiveChart.SeriesCollection(1).XValues = "='Pair Data'!R2C2:R"&days+1&"C2"
how I can fix it? It highlights the "C2" at the end and says Expected End of Statement.
I want to do is be able to take The data from one week for every store and compile it into a chart to show a summary of the company as a whole. I know that I can reference from that chart to each worksheet the values I want, but how would I give that chart the ability to update each time I add a weeks worth of data.
I have attached a file that shows what I am talking about. The sheets labeled Waterloo, Mogadore, through Green are the sheets for each store.
I want to take the last weeks data and put it into a chart as seen on Sheet1. Then when I add data for 3/21/09 I want to make a new chart without retyping the data into the chart.
I have the following code:
[Code] ..........
However, whenever I run the code, if there's even on word that is bold in the cell, then the entire text string in the cell turns bold. How can I stop this from happening? i want to keep the format of the text string the same, only remove and replace the items listed in the code.
I run a MTD sales report from Access. If the "SOP Type" in Column Q, is a "4", I have to manually change the Qty and Price to negative values as that "4" denotes a Credit. My question is this...
Can I write a Macro that would be able to read the value in Column Q and, where necessary, make the values negative in the appropriate columns?
I am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.
If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)
Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?
I have the following code to create a 3D line graph:
View 2 Replies View RelatedI have a simple line chart but I want to add an text description to each of the points. The data is driven by a pivot table so the text needed may change based on the drop down options.
If I was using the below data I would want the line graph drawn using the date and value, but the data label at each point to state the description.
Date Value Description
01/01/2010 90% Text A
01/02/2010 80% Text B
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I have never used charts in excel unfortunately. I just finished watching a few tutorials but still a little confused. I have a WS with a list of items I want to make various charts of how do you make a chart that adjusts when new items are added? (thus dynamic data) Do you need a macro to do this or is it just standard by defining a range within that sheet?
View 7 Replies View RelatedI have data:
Jan Feb Mar Apr May
2009years 56 45 42 50 62 - series1
2010years 48 47 45 48 55 - series2
I need make line chart from 3 series. First two are series1 and series2. Third is percent change between 2010 and 2009 years every month (series3). First two are easy, but third is very difficult for me, becouse I haven't row with percent change and I can't haven't it in my exercise.
How can I make it without new row with percent change?
I tried it with Data Source in chart, also formula in Name Manager, but it doesn't succeed
I would like to update a chart after I have updated the underlying data just by clicking a button and displaying the updated date in the chart. Does anyone know how to do this? Currently I am having to manually click on the graphs line in the chart, which highlights the columns data, then manually extend this for the chart to update. And then I am manually typing in a date cell in the chart. There MUST be a cool way to do this.
View 6 Replies View RelatedIn a chart series, I have and end number for a row that I have been updating manually everytime I enter a new fuel tank fillup. Is there a simple way with the chart series to just say always go to the last data in the specified column?
See my example here, instead of the 137 in the series is there someway to just say "last data row"?
[URL]
I'm trying to plot a chart of some sort in Excel to display 4 angles that different blood vessels branch off the aorta. Ideally I'd like to input the values of these angles which would vary from patient to patient and have them be displayed in some kind of chart. The chart I'm hoping to achieve would be cirular with the 4 angles represented by spokes which extend out all of the same length. I've attached a rough sketch to show what I'm trying to achieve and I'd be hoping to be able to change each angle in a worksheet and have the chart update to reflect.
View 9 Replies View RelatedOr at least with a formula?
This is the chart formula for the report:
=SERIE(Report!$B$10;Report!$A$11:$A$30;Report!$B$11:$B$30;1)
I have a chart with percentages and most of them are >40% so I would like te values to start at 40 in the y axis but I would also want to have the 0% show (as it stands there is nothing there) It is a double y axis chart where the % is shown with just a dot and the other Y axis is the actual column. Im not sure if I'm making sense but I would just like to have the 1st value in the secondary y axis be 0 and the 2nd value be 40, and from that point forward scale in intervals of 5%.
So the values in the y axis are: 0%, 40%, 45%, 50%......
I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.
Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. What I mean as you open the example sheet and start messing around with the data validation boxes.
The problem I am encountering now, is creating a chart that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.
Here is what I am looking for in the graph:
1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.
2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.
3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.
we all have a team, and we are scored on calls, appointments, demos, proposals, and revenue. Rather than asking us to do one or all of these metrics, I would like to have a bulls eye chart that could show people their bonus eligability, so if for example 50% of the circles are touching the bulls eye circle, you would be able to adjust what you need to work on for bonus.
Here is a sample graph.
sample structure.jpg
Here is sample data I am trying to work withsampledata2.xlsx
I have a project coming up where I will receive tables in a certain format. I want to be able to create a macro that well create a chart based on a single inputted cell (start of data). The chart will have 4 series and five values per series. See the link below for sample data! The first data point is not in the same place in the sheet, but the table will have the rest of the data relative to that!
[URL] .......
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
I have 6 pivot charts within a document. I use a macro associated with a button to refresh the tables and therefore the data displayed on the charts.
However, each time I refresh the data, the 'data label' font size also refreshes. What is particularly odd is that 5 of the charts are pie-graphs, of those 5 graphs, the font size only resets on 4 of them (with the data labels in a single chart staying at my desired font size).
I can manually put the font size back to that which I desire but I'd rather it just didn't change in the first place.
I've had a look to see if there's any noticeable differences between the 5 charts but there's nothing which I can find.
I'd be happy to run a bit of VBA to set the font size as I want it, if there isn't a better solution.
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name
1/01/2014
2/01/2014
3/01/2014
6/01/2014
7/01/2014
8/01/2014
9/01/2014
10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
I have a range that contains text. Let's say the range is colums $F:$M. There is x rows and in each cell there is either nothing or a word, let's call it "dummy". There is just a certain amount of "dummies", such as "dummy a", "dummy b" and so on, let's assume ten different dummies. It can also happen that "dummy c" or any other dummy appears more than once in those colums.
What I want Excel to do, is looking up columns $F:$M and make a list (possibly sorted) that includes all the entries from columns $F:$M, but each one only once and no blanks. Can I do this and if, how?
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.