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.
I have a spreadsheet that calculates a value for each age up to 65 on a separate row. I have parameterized the spreadsheet so that depending on the age entered by the user, any row below the age 65 calculation row is blanked out (set to "") with an if statement.
After a new age is entered I would like a line graph to compare the values. My problem is that if I select all of the possible data values (I assume none are less than 20, therefore there are 45 rows of formulas) any rows that have blank values show up in the chart. My data starts in cell A1.
I would like to come up with a macro that accomplishes the following . . .
- determines the number of the lowest row that has a number value in it (since there are formulas below this, Ctrl down does not do this correctly)
- set a named range starting from cell A1 based on x rows (calculated in the prior step) and y rows (constant)
I've just read up on interactive charts and have got that sorted for my worksheet. But I have a slightly more complicated issue now...
The worksheet Logistics has results for 76 different tests and these tests have been taken multiple times. Hence, I can logically have a trendline of results for every test i.e. Test 1: 1/1, 2/2,3/3 etc...
I'll go nuts creating 76 charts...and it's a terrible messy way of getting the user to see how he's fared over the number of times he's taken a particular test.
What I'm looking for is a way for the user so indicate which test he wants to see the results for and the relevant results to come up. In other words, on choosing the trendline of results for a particular test. Possible?I saw something similar on a website but am not sure if it was relevant...
I am trying to learn how to use offsets for dynamic charts and having trouble getting the formula to pick up the correct values.
View 3 Replies View RelatedI 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'm running a chiropractic office. In order to see where I've been and where I'm going I keep track of things like new patient visits, patient report visits, cash in, insurance in, etc. I keep track of this on a daily basis on one worksheet. The weekly totals are paste-linked into another sheet. It is this sheet that I would like to use to create a dynamic chart however, in using the OFFSET function to create the chart, the formula counts the 'blank' cells b/c of the paste-link from the previous worksheet.
View 1 Replies View RelatedI 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 created a dynamic chart, courtesy of the Jon Peltier website. I have 2 problems. No problem with getting the chart to work, the problem comes when I delete the data. I get an error message telling me that 'A formula in the worksheet contains one or more invalid references.' The other problem is that I cannot copy the worksheet to another worksheet in the same workbook. Well that is not strictly true, I can copy it but it wont updat. Any ideas as to how I can overcome this? Any suggestions welcome, apart from 'Don't delete the data then'
View 9 Replies View Relatedi want to create dynamic Excel Charts in one sheet ,The sheeet contain dynamic columns and rows and i want to create for each 4 columns a chart in a new sheet ,i started to try it in VBA and didnt founs the way to select 4 columns as a range, i succeseed to do it with msgboox that the user enter the range but i want to do it in a loop
View 9 Replies View RelatedI 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 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]....
building dynamic charts and filters, so What I'm hoping to accomplish to build a bar chart that looks like the following:
________________________________________________
Filter: State | Filter: Region | Filter: Segment |
Revenues |||||||||||||||| + (% of Total Rev)
COGS ||||||||||| + (% of Total COGS)
Margin |||| + (% of Total Margin)
-------------------------------------------------------
My data is built in the following format on a different tab than the proposed chart:
State: | Region: | Segment: | Account: | Amount: |
MN | Midwest | Major | Revenues | $$$
MN | Midwest | Major | COGS | $$$
MN | Midwest | Major | Margin | $$$
MN | Midwest | Major | % of Total Revenues | %%%
MN | Midwest | Major | % of Total COGS | %%%
MN | Midwest | Major | % of Total Margin | %%%
[Code] ........
It seems like it'll be easy to use a pivotchart, but at the same time, there's a lot of formatting to do, which makes it tricky, especially if I have many states to deal with and the multiple combinations.
There is POLICY statement that determine an what ACTION should be applied to a paticular building given a score for "LIFE" & "RISK" which results in varing policies A-D. (The Policy intervention points differs slightly for varing types of building. This excel sheet works just fine, and provides the correct guidance for our engineers by suggesting Policy A-D on input of tbuilding type and LIfe/Risk score. However the Policy is a guide, and they do need to apply a level of judgement with the data close to intervention points.
My Question,
I would like to provide further graphical representation to aid decision making. I have a graph plots the X Axis ( LIFE) 0-100, and y Axis (risk) 0-5 and show the , but what i need to be able to do is dynamically adjust the position where the x&y axis cross, so showing visually the intervention point when different building types are selected.
I am using defined names to build a chart. This is working fine, but I also would like to use a defined name to control the +/- fields for the error bars. The defined names I have set up are correct, and I am able to enter them into the sorce data form but Excel does not display them.
View 9 Replies View RelatedI'm trying to dynamically populate ranges to facilitate dynamic charts being generated.
I use excel 2010 at work, and 2011 for mac at home.
Dynamic chart ranges populated from named ranges as selected in nested indirectly sourced validation lists
I want any selection made in a dependent validation list which contains a list of named ranges to trigger a worksheet_change event which copies the range the selection points to and pastes it into a dynamic range in another column, beginning as a specified cell.I've tried using this, put together from some code examples from similar, but different issues.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
Range(Range("B2").Value).Copy
Range("P2").Paste
End If
End Sub
Trouble is, I don't really understand this code. It doesn't appear to do anything when I make a worksheet change in "B2", but I don't know exactly what it is. I suspect that perhaps the fact that "B2" is validated from an indirect source might be difficult?
Today() provides a dynamic date which always equals the current date - not very useful when dating accounting documents. Is there a function that will allow the use of Today(), at the time of the document creation, but will then freeze the date to the date of creation?
View 5 Replies View RelatedConverting formula to static code. I am currently using the following code on a spreadsheet:
View 3 Replies View RelatedI have never done this before and I am having a hard time - it seems that none of the graphs in the Excel menu accommodate what I want and I have no idea what I am doing to boot.. Could someone PLEASE help?
My data is: American & Japanese workers can each produce 4 cars a year. An American worker can produce 10 tons of grain a yr. / Japanese 5 tons of grain a yr.
Each country has 100 million workers.
Need PPF and Opportunity cost of car & grain from U.S and Japan.
I have at this for two days and I cannot get the information in the chart the way it is supposed to be. It makes no sense evry way I have tried it and the graphs don't even look correct. I've tried several line graphs and scatter graphs..
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.
I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.
I have 2 charts on one sheet. Both the charts are generated using 2 different VBA code. I have also created a Button to trigger the code and generate chart when the button is clicked.
I recently realized that I was creating charts upon charts and was making the excel file bigger in size all the time.
So, I wanted to see if I can delete the individual chart before generating a new one.
Currently, I am using the following code to delete the charts.
I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....
View 12 Replies View Relatedif marco recording of chart manipulation has been reintroduced in XL2010
View 6 Replies View RelatedI have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.
View 5 Replies View RelatedI don't mind paying for the add-on/add-in, I need to have a chart displayed of 100 rows and 1000 cells of data.
View 6 Replies View RelatedI've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on.
I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc.
That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages...
Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos.
I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean.
I'll only follow the changes within a year so prices older than a year will not be shown in the charts.
Now I need to create multiple pie charts based on data in tables on many sheets.
I need to create the same set of charts, for each sheet separately.
I recorded a macro for one sheet, which created following
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8"
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
ActiveWindow.SmallScroll Down:=3
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime.
It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data.
I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data.
How do I generate an empty chartobject everytime, so I can insert seriescollection info with code?
Any other way to generate charts?
Excel / Word 2003.
I am exploring yet another avenue of vba coding that i am not familiar with.
I have a template word document that i am wanting to fill with items (be it cell values or charts) from an excel file.
I am looking at the coding examples on the net and i am slowly getting there. However i seem to be unable to copy a chart from my excel file and copy into word. I need to be able to specify a specific position on the word document too.
Has anyone seen this problem before;
I used a "MID" formula to extract some data and for some reason the chart I'm trying to use won't recognize the data. Here is a copy of what the data looks like. Non of the vaules will show up on the chart.
DPJE2DD2CE2CJan1.2784.8984.8997.65Feb1.2697.6597.6591.79B10==MID(B$3,8,5)Mar1.3091.7991.79154.7Apr1.28154.7154.7147.6May1.29147.6147.687.78Jun1.4387.7887.78112.6Jul1.39112.6112.6115.2Aug1.42115.2115.293.6 Sep1.3393.6 93.6 138.9Oct1.28138.9138.9NovDec115.6YTD1.32115.6115.6
How do I get my charts to update everytime, the data refreshens...I have my series linked to several worksheets within a workbook.
View 9 Replies View Related