Use Offsets For Dynamic Charts
May 10, 2006I 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 RepliesI 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 RepliesMy 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 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.
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?
In short I have two sheets.
Sheet1 ("Test") has 8 columns with the Customer # in column A.
Sheet2 ("Evaluations") also has 8 columns. Customer # in Column A and date of service in Column B.
What I would like the macro to do, is to read through the customer column on Test, then for each customer, paste ALL dates of service from the "evaluation" sheet. Customers have multiple evaluations, so it pastes the first one into the 9th (I) column, the next into column J, etc.
The end product is a "Test" sheet with one customer on each line. Columns I and beyond have all service dates for that customer.
It seems like the below code should work. For some reason, it throws about 100 evaluations onto the first customer, even though they only had one.
or error corrections?
Sub Test()
Dim csr, incrmt
Dim d As Range
I'm trying to figure a way to calculate numbers in arrears as a part of a sum/average/stdevp.
What I've done so far is create a named range called "x_values" that dynamically adjusts itself to the dates given. What I'm trying to do is create a range that finds the avg of each "X_VALUE", and the previous X_VALUE before it.
I tried =offset(x_values,0,0,roll_factor,1) to do the offsetting; but it just starts at the very top of my range .....
I have now a formula that multiplies 32 row offsets. The no of rows of offsets are in CN31:DS31 and the references for the offsets are in DY$71:FD$71. The row offset values range from 1 to 11.
=OFFSET(DY$71,CN31,0)*OFFSET(DZ$71,CO31,0)* ... *OFFSET(FD$71,DS31,0)
There are close to 800 characters in the formula. I want to expand this to a multiplication of 100 factors. This would not fit in one formula, and creating more formulas is tedious and possibly subject to errors. It should be efficient, since this formula occurs 12000 times in my spreadsheet.
I am trying to copy three columns of information from sheet1 into sheet2.
When pasted I need the information to be spaced or offset as per below sample.
Sheet1
HTML ABCDEF
1#AnimalDescription
21Dogfour legged fur ball
32Catother four legged
43Horsegsdfgdfgdbfb
54Snakedfgegergeg
65Ratlkjlmlbnm,
76Birdtshgjmgjkk
Sheet2
HTML ABCDEF
1#AnimalDescription
21
3Dog
4four legged fur ball
52
6Cat
7other four legged ...................
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]....
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies View RelatedI have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
I 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 Related