Excel Dynamic Chart #9: 4 Week Chart Dynamic Formula & Dynamic Data Validation Formula

Howdy All!

I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.

The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).

However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.

Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?

I've tried using trend lines, but they don't actually average...so they're not very helpful.

Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...

Thanks for the help.

Hi,

I have found articles that show how to make a Dynamic chart using Named Ranges and Offset (link below) but that will not work form what I am trying to do.

http://www.get-digital-help.com/2007...dynamic-chart/


I have a Series of data part of this data will be updated every week and the other part is Static and goes out till 2012.

What I would like is that that chart updates to add more data every week when the new week starts


So for example next week the new data would populate in the Chart (along with the Static data) So on the week of the 20th new data comes in the chart Automatically because the week started and so on and so on Formulas and or VBA is fine for this.

Here is the Example Data (does anyone know a good program to Post the Sheets? the HTML maker I had stoped working)

B C D E F
Date Actual Forcasted 6 week Avg Avg
8/30/10 2.53% 3.28% 3.52% 4.47%
9/6/10 3.51% 3.28% 3.52% 4.47%
9/13/10 4.07% 3.28% 3.52% 4.47%
9/20/10 5.26% 3.28% 3.52% 4.47%
9/27/10 6.33% 3.28% 3.52% 4.47%
10/4/10 5.21% 3.28% 3.52% 4.47%
10/11/10 7.39% 3.28% 3.52% 4.47%
10/18/10 5.60% 3.28% 3.52% 4.47%
10/25/10 3.56% 3.78% 3.52% 4.47%
11/1/10 3.82% 4.73% 3.52% 4.47%
11/8/10 4.17% 4.73% 3.52% 4.47%
11/15/10 1.17% 4.73% 3.52% 4.47%
11/22/10 2.90% 4.73% 3.52% 4.47%
11/29/10 5.49% 4.73% 3.52% 4.47%
12/6/10 0.00% 3.09% 3.52% 4.47%
12/13/10 0.00% 3.09% 3.52% 4.47%
12/20/10 0.00% 3.09% 3.52% 4.47%



thanks a lot in advance and please let me know if you have any questions

I have designed a couple of graphs using named ranges to give dynamics to the number of series in a chart. This works fine as long as I don't want to copy and/or move the sheet to a new work book. When I do this, Excel chooses to remove all references to the named ranges in the SERIES forumla and replace them with fixed references (despite having scoped all named ranges to WS scope).

From googling I found the tip to save the WB as a template and use Insert sheet to add the graphs. This does not quite work as expected though. The SERIES formula points to the right named ranges, but when I update data the graph is not updated. If I save the new workbook and reopen it, it works as expected. Other quirks include not being able to Insert a second template without saving and reopening (the named ranges points to wrong sheet if I do not do this).

So my question is two-fold; has anyone seen this problem before and solved it? Or, has anyone been able to successfully copy/move a sheet and keep the named ranges without involving a "relink" macro?

Is it possible to create a dynamic chart which will accommodate the addition of an unknown number of additional dynamic series using Excel 2007?
That is, I have a db query feeding a table and there will be new rows added each day AND new columns:

............Employee 1.....Employee 2.....Employee 3.....Employee 4.....Employee 5
Jan 1.....10....................5
Jan 2.....9......................1
Jan 3.....12....................11..................8.....................55
Jan 4.....14....................12..................10...................1...................1
Jan 5.....19....................13..................13...................11.................10
Jan 6.....12....................15..................16...................16.................18


As you can see, there is more than one series and new series come on line all the time as new employees are added.

I can easily define the dynamic range:
rowscell=counta(a2:a9999)
columnscell=counta(b1:b9999)
myrange=offset(mysheet!$a$1,0,0,rowscell,columnscell)

However, when I try to use myrange as the graph data range I get a "The formula you typed contains an error" dialog.

Any suggestions?

Hello,

I have a workbook that I am developing which will use named ranges to graph different data depending on what is selected from a drop down box.

I have most of it worked out, i've just hit a snag when entering my named ranges into the chart series.

my first named range is "Date". The named range links to a range which is on a different sheet to the one in which i'm putting the graph. when I type "='Book1.xlsx'!Date" into the series x values and then click out of it I get an error saying something about a formula reference.

I've checked to make sure that the named range works by selecting a bunch of cells and entering it as an array and all seems to be fine. I've searched for answers in a lot of places and everything just ends by saying to type what I have into the series box and it works...except mine doesn't.

There also appears to be another method involving the SERIES formula but im not sure how to use it because nothing i've found is clear on how to use it. They just say to enter it into the values box and show a screenshot of what looks like excel 98...im using excel 2007


If anyone can help me out here that would be great

I have a sheet in a workbook that contains a list of categories for recipes (like chicken,beef,italian,mexican,chinese,american,etc...).

In that same sheet there is a list for each of the items in the categories list. These lists contain references to recipes that fit under that specific category.

These named lists are dynamic as values (recipes and categories) can be added to or taken away from them based on text files that are used to update (add to) the sheet.

Another sheet references this named "categories" list via data validation.
Depending what category is selected in this first data validation cell, I have a second data validation cell that references the list for the selected category.

The code I have works for this so far (it's pretty simple due to the named range naming convention I used) but would like the value in the cell to default to the first item (recipe) in that category.

Here's the code that works:
Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim list As String
Dim lname As String

    'Get the name of the range that the
    'data validation cell is to refer to
    lname = "Type" & Range("C2").Value & "List"
    list = "=" & lname
    'MsgBox (list)
    Range("C3").Validation.Delete
    Range("C3").Validation.Add _
        Type:=xlValidateList, _
    Formula1:=list
End Sub

I've tried adding the following just before the 'End Sub' line but none work.
Can someone tell me how to reference the first cell in a named range?

Code:

Range("C4").Value = Range(lname).Value
Range("C4").Value = Index(lname, 1, 1)
Range("C4").Value = Index(lname, 1)
Range("C4").Value = Range(lname)(1).Value
Range("C4").Value = Range(lname)(1, 1).Value

Edit: I've also now tried this, but it still doesn't work:
Code:

Range("C4").Value = Range(lname).Cells(1).Value

Hi

I have set up a chart very similar to one in this page (I used the info there to create my chart, thanks Rob and Andy, rep given)

http://www.excelforum.com/excel-gene...h-rolling.html

Basically it automatically updates when new monthly data is added and adjusts the chart accordingly displaying only the previous 12 months data on a rolling basis. (Well it does that once the answers provided on that page are incorporated

Is it possible to have a validation to select the month which then returns the chart info for the rolling 12 months instead of having it update automatically when the data is input?

Many thanks

I want to show the last 10 data points rather than all, the formula below will show the last 10 when the data is vertical, what would the formula be if the data was horizontal?

=OFFSET(Sheet1!$A$1,COUNTA($A:$A)-10,0,10)


For example...
If my x data was in A1:Z1
and my y data was in A2:Z2


Thanks

I'm trying to create a chart that would illustrate the change in an individuals net worth over the course of their retirement, from retirement age to their death, with age on the x-axis and net worth on the y-axis.

In B2 i have the retirement age linked from another sheet. In B3 and down, I have the following formula: =IF(B2<Summary!$J$8,B2+1,""), =IF(B3<Summary!$J$8,B3+1,""), etc..., with Summary!J8 being the linked illustration age or the age of death.

In C2 and down, i have the net worth that corresponds to the age in that particular row:
e.g. =IF(B3="","",Summary!L46), =IF(B4="","",Summary!L47), etc. where summary!46 is the linked net worth from another sheet

In a typical situation, there will be 5-10 rows that go unused and are filled with "". I want to create a chart that will only show the cells currently in use and not filled with "" and automatically update the x and y-axes if there is a change in the illustration age. Say, for example, a client wants to see what their net worth at death would be if they were to die at 90 instead of 85, the column chart would automatically update the x-axis to go out to 90 instead of to 85 and fill in those corresponding net worth values.

Is this possible? I've spent all day fooling around with offset to no avail and was hoping somebody could point me in the right direction. If I haven't made something clear, just say so and I'll try and explain it better.

Thank You

Hi,

When i try to use the below dynamic formula in Data Validation i get a source error message. I tried to use the below as a list so i do not get any blanks in the list dropdown.

=MATCH(REPT("z",255),Assumptions!$E:$E)-ROW(Assumptions!$E$19)+1