I have a chart in excel, I want to click on the chart and run a macro that will return the source data location/ range (e.g. "A1:C12"). Assume you do not know the range, but only see the chart. I tried using activechart.setsource.select doesn't work. If i were to click on another chart, run macro and the range will be saved into a variable where the variable is holding the range of the source data location.
View 4 RepliesDates
1/2/2006
11/29/2007
7/3/2008
Table:
Year Start Date End Date
2005 01/30/2005 01/28/2006
2006 01/29/2006 02/3/2007
2007 02/04/2007 02/2/2008
2008 02/03/2008 01/31/2009
For each "Date", I need to scan the table and find which "Start Date" and "End Date" range does it fall into and return the applicable "Year". For example:
1/2/2006 will return 2005
11/29/2007 will return 2007
7/3/2008 will return 2008
I have 2 works sheet.
Sheet 1 is a table, sheet 2 is the source data. On sheet 2 the source data, are two columns. Column A has a range of codes , column B has a range of dollar values. Each code has a different corresponding dollar value.
On sheet 1 I would like to enter a code in column C and have it return in the cell to the right in Column D, the corresponding value from sheet 2 column B.
Column C Column D
E4 $145.78
E7 $89.01
I am looking for a function that will search a range like 101-199, 200-249, 250-299 and so on.... Say I have a list of numbers like 155, 179, and 210. How can I find out what range they belong to? I am kind of looking at SumIf's but I cant seem to get that to work.
View 3 Replies View RelatedI have attached a file to make this easier. Basically I would like to search "Points Scored" (B2:W9) for the highest score (which I already have done) and when it is found return the team's name that is associated with it. Then do this for the second, third, etc. as it can be seen in the "Main" sheet. I'm thinking an index/find approach, but I just keep getting "N/A".
View 7 Replies View RelatedI have data that has months in it that are spelled wrong and in different formats etc. I need to be able to search the range of data I have created in a separate table in order to return the correct abbreviation into my spreadsheet. See below:
Need to be able to search this range (A1:E12) and return the corresponding horizontal value in the last column (Jan, Feb, Mar)
JanuaryJanuraryJan-11JanJan
FebruaryFebruraryFeb-11FebFeb
MarchMarchMar-11MarMar
AprilAprilApr-11AprApr
MayMayMay-11MayMay
JuneJuneJun-11JunJun
JulyJulyJul-11JulJul
AugustAugustAug-11AugAug
SeptemberSeptemberSep-11SepSep
OctoberOctoberOct-11OctOct
NovemberNovemberNov-11NovNov
DecemberDecmberDec-11DecDec
I am currently trying this, but its not working! =IF(F2="","",VLOOKUP(F2,Dates!$A$1:$E$12,5,TRUE))
I am trying to use a VLookup to pull a value out of a range and return my intended data. See attached spreadsheet. The data I'm pulling from is in the top chart. The bottom chart, in the factor column, is where I want the data to pull to. Ex) If the "Months of Development" column in the bottom chart =13, I want it to pull the factor value from the top chart where the month is 13. The problem is that the top chart has a range of values in the "Month Range" column of 13 - 24. I don't know how to get the VLookup to recognize this and pull the same factor into the chart below whether the value is 13 or 24. Maybe VLookup isn't the best option here.
View 2 Replies View Relatedi 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.
I have a chart that I want to be the same across multiple worksheets. The data ranges don't move, but the data may be different. It is cumbersome to go and retype the name of the sheet every time this chart is placed.
I have tried using named ranges. My named range X is !$A$1:$A$30 so that it will refer to the active sheet. If I place this in cells on the spreadsheet, it works. If I place "=X" in the values entry for the source data of the chart, I get a formula error.
I have a Defined Dynamic Name which is used to select my data. How do I call that when I create a chart in excel within the data source, so that the data source always refers to this dynamic range.
I have data A B C D E
1 2 3 4 5
9 4 5 6 7
this is so the series names will be the row and for each series the rows represent the data within each of the columns. I have a dynamic range that always selects the whole range called chartdata. How do I put that in the data source when it asks me in Excel 2007 as there is no wizard for seprate x and y ranges in this version.
i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different.
The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work.
What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.
My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers
Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
In the attached document is a timeline made from a scatter chart. Error bars using custom values are used to show the length of each task, however I can't get the chart to include error bars for the last 2 data points (tasks).
View 1 Replies View RelatedHi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies View RelatedIf there a way to change the souce data so that it changes based on a variable? For example, if a cell's value equals X then use one range and if it is Y then use a different. As far as I can tell you cannot use the "IF" argument in a chart Source field.
One option I came up with is to change the actual data on a worksheet and always have the source data point to that range in the worksheet. The problem is that the source data is not always the same length (Monthly may be 100 columns long while Daily is 1000 columns long - the chart looks starnge).
for example: ...
I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select
[code]....
excel data table within the charts. I would like to customize the data table placing underneath the chart so I can highlight (say, in red) some of the figures that are greater than certain values for easy reference. Is there anyway I can do with it (since I got more than 800 charts) than that I can only use tools to draw a rectangle and cover that particluar cells up? or is there any ways I can link up the conditional formatting of the data itself with the chart so I can applying certain coloring on the data and reflect on the data table?
View 3 Replies View RelatedI have three items I would like to graph, on one chart. The first is %Complete. This is a number figure. The next is the date the item is due to ship. Obv, this is a Date. The third is today's date. Basically, I would like to display in the same chart so that we can see how far a long a project is, in relation with today's date and how much longer we have until it needs to ship.
This is the current display of the chart:
Is there a way to show the ship date, as the 100% complete mark? In order to make it display half-way decent, I had to format the left y-axis to go above 100%. I first tried to format the right y-axis, but was extremely confused to setting values. I figured out that the end date is somewhere in the 40,000 value mark? WTF is this about? Is 1 equal to 24 hours? I assumed that was the case, but then when I pick the value it should end with, it doesn't seem to react that way. I want the right axis to start with 12/01/2011 and end with 5/01/2012. How can I achieve this?
I am creating dynamic charts with a data sheet that is horitzonal. My x-axis : =OFFSET('chart data'$B$1,0,1,1,COUNT('chart data'$2:$2)-1), y-axis: =OFFSET('chart data'!$A$1,0,1,1,COUNTA('chart data'!$1:$1)-1). My formulas are giving me #VALUE. Not sure why.
(formatted general not as dates)
1980
1981
1982
1983
1984
1985
1986
1987
1988
CONSUMPTION
70
61
67
83
89
75
67
71
71
I am trying to record a macro so i can click a button and have excel break the link between my charts and the tables that drive them. during the record phase, i click on each chart and use F9. the problem i am facing is that now my macro sets the charts to the data that was there when i recorded the macro, erasing any changes i have made.
View 9 Replies View RelatedCan i create a chart in excel just using a formula - but not data?
View 4 Replies View RelatedHow can i link colors used in charts to companies i have i my database. Now excel provides colors in a certain sequence but this is not what i want. I attached a file in which i for example wants to create pie charts of the company's sales. What do i have to do to give each company its own color?
View 2 Replies View RelatedI'm currently working on a spreadsheet for my husbands work in a school environment. All was going well until I hit a wall with the alphanumeric data of the current uk stats system. Is there any way of getting my 4c...4b...4a...etc. to chart?
View 1 Replies View RelatedI've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.
I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).
The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.
This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "$0.00" for periods of time where nothing has sold.
To have a Price per square foot of $0.00 is incorrect since in reality nothing was sold.
I would like to be able to eliminate the portions of the graph that have the "$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.
I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?
I need to transfer data to powerpoint from my '4 box' reports in excel.
Essentially copying everything in the range A40:U74 inc charts and paste them into a slide as a picture on the click of a macro button.
I have to do alot of reports like this for my project and it takes up alot of time copying and pasting etc.
I have seen different codes for this but they are either for charts only or text... the one i did see for both, didnt work at all.
I'm trying to create a bubble chart created on three different columns of data. I'd like the following:
-The X-axis should just show the first columns of dates I have stored.
-The Y-axis should be the number of lots of a product that I bought of a certain product.
-I'd like the actual bubbles to not only represent the number of lots purchased on the given date, but I'd like the size to be represented by the third column, which is the average price of each lot.
Is there a way to do this? I've been trying to maneuver it around all morning, and can't seem to get it how I want it. I'm not sure if I'm ordering the columns incorrectly or what.
I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.
One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.
Name
1 2 3 4 ...
A
B
C
D
E
F
For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.
Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?
I've got several sets of data all which link to seperate charts, the problem i'm having is that i dont want the chart to display the fields with zero values and with the amount of charts and data it is taking ages to re-source the data is there a way of getting the chart to ignore zero fields or of somehow sorting the data in a way only fields with values are shown
View 9 Replies View RelatedProduce a chart (not a problem) that can be updated should the length of the X and Y data change?
This may not even need to be a macro, if it is possible to have a chart whose X and Y axis updates depending on the contents of a column?
If you search through the history of even this forum you will see quite a lot of people are looking for this solution.
I am wondering if there is a way to take the charts I have created, and save them on a different worksheet in Excel. The issue I have is that when I erase the data source, the charts lose all their data. Is there a way to save the chart as is without it relying on its data source (ex. So I can remove the data for the chart)?
View 2 Replies View Related