Dynamically changing series in a chart (sort of) (

Topic

New Reply

Hi Kislany,

There are many different ways how to achieve that.
Attached is a very simple example.
If you change the week-number (last) in A3, the output table will change accordingly.
You can create a chart based on the output table.

Best regards

Wolf

Reply | Quote

Replies

I’ve come across an interest question that somebody asked me,. but of course, I have no answer to it, I am not even sure it can be done.
There is a table with weeks (ex. week1….week7), out of which a chart is created, whereby the weeks are in the x axis. Every week a new column is added, so next week we have week 8, or week 9, etc. My colleague wants to always have in the chart the latest let’s say 7 weeks displayed, starting from the most recent week added. So when week 10 is added, we want the chart to display week 4 -> week 10, when week 11 is added, then week 5 -> week 11 (so it’s like drop one, add one), etc. Right now she is doing this manually by editing the chart, deleting the first week and adding the last week as the new series. Is there a we could automate this process somehow, with a formula maybe?

I forgot to mention that I have found a ‘tentative’ solution from Erlandsen’s ‘plot the last 12 weeks’, which would work perfect, except that it doesn’t Reason is that the cells that make up the table are all linked from another file, as it’s a very big report. Erlandsen’s chart works when the last row has been populated and you go and add in manually a new row of data, so the dynamic range gets expanded. However my cells are not empty at all, all the weeksof the year have beeen populated in advance with linking from another file, so if we’re in week 10, week 11 is displayed with a 0 (as it’s the link to something else).
Given this added problem, any solutions maybe?

Thanks!
Kislany

Reply | Quote

You can still use a dynamic range chart, you just have to change your calculation of the offset to ignore the zeroes.

Does this do what you want? I assume that the data will only be zero when there is no data. If your values can actually be zero it will not work and we will have to give me some logic to understand when a zero is valid and when it is not…

Steve

Reply | Quote

Wow, that’s great, thanks a lot Steve, this is just what I need! It’ll take some time to do it, but all I need to do is create the name ranges and change the series in the chartsa. I’ve done a small testing and it works!

Thanks everybody else for helping me, I will go with Steve’s tip, as this requires the least extra work from the users (creating new tables, or adding VBA). I will modify the original sheets myself, and the users won’t even know that anything has been done to it.
Kislany

Reply | Quote

This isn’t working for me. I’m using Excel 2003 on Office XP.

I have three columns and three dynamic named ranges in my worksheet, which receive data from queries updated on workbook_open. One is for quarter-end dates, one for total Added, and the third for total Deleted.

The named dynamic ranges are as follows:
QDate: =OFFSET(qTmpTotSelect!$A$2,0,0,COUNTA(qTmpTotSelect!$A:$A)-1,1)
Added: =OFFSET(qTmpTotSelect!$B$2,0,0,COUNTA(qTmpTotSelect!$B:$-1,1)
Deleted:=OFFSET(qTmpTotSelect!$C$2,0,0,COUNTA(qTmpTotSelect!$C:$C)-1,1)

(I start with row 2, because row 1 contains field names)

In the worksheet cells, I can use =sum(dynamicnamedrange), max, etc. with all three named ranges, and get correct answers. So far so good!

But when I try to use them in my chart ie, =SERIES(qTmpQTotSelect!$B$1,QDate,Added,1) , I get an error message:

“A formula in this workbook contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference. ”

Anyone know why?

Thanks!

Reply | Quote

You must add the filename before the named range, so it knows where it is:

=SERIES(qTmpQTotSelect!$B$1,’Filename.xls’!QDate,’Filename.xls’!Added,1)

[of course, substitute the actual filename for “Filename” in my example]

Steve

Reply | Quote

Thanks. I didn’t actually have to use thr file name, but I figured out (through trial and error) that you have to include the sheet name and bang operator before the named range, even if the chart is on the same sheet!

go figure . . .

Reply | Quote

That also works. Notice that XL converts the “‘sheetname’!Added” to “‘Filename.xls’!Added” nomenclature…

Steve

Reply | Quote

This idea is great. Is it possible to use more than one set of data for each week? For example, tracking temperature and percipitation. Also in this case zero could be valid in this case.

I’ve played around with your datacount name, but it doesn’t appear that you are able define it over a number of columns. Tried $b:$c in your formula.

Thanks.

Reply | Quote

You can just create another named formula and and that name to the chart. It can use the same datacount as the others. If it could have a different length, and zero is “allowed” how should I tell when the list is “done”?

Steve

Reply | Quote

If this works with minus temperatures, why does it not read zero as a temperature?

Reply | Quote

You said that zero was to be for “no data”. If your data is going to contain zero as a number, then you need to use something else to indicate “No data” (for example an #NA error or test. The formula works by not counting zeroes.

If you use an error (or even text) the countData name can just be a count of the data rather than a countif.

Steve

Reply | Quote

Hi Krislany

I thought I’d just throw this into the pot too… The only extra in this thread starting at post 394,337 and in Hans’ reply in post post 394,345 is that the chart includes a scroll bar that allows the user to view any week of data by scrolling back and forth through a very dynamic chart. Have a look at the example, and it you find it useful…the instructions to design the scroll bar are included.

Cheers

Reply | Quote

Thanks a lot for your quick help. It is a good example, except that it would complicate managing the data, because of two main things:
the table that is used to create the chart is already a huge one, and it is linked from a variety of sources. Actually there are 3 big tables in the sheet, each used for a separate chart. The second issue (should we still go in this direction, which we might if nothing else comes up) is that the week names are text, and not number (ex. w41OCT05), so the formula does not work correctly. How can we modify this formula to count 7 up from there?
Thanks again for your help.

Kislany

Edit: Ah ok, I think I found the offset for counting up, I tried this :=OFFSET($C$20,-7,0) and it worked, but that doesn’t seem to help much now, as user cannot input the week number anymore…*sigh*

Reply | Quote

See if the attached can be adapted to your requirements. There is a command button on the worksheet that will update the chart.

Reply | Quote

Thanks Hans for the example, it is definitely in the right direction. I have tried to modify it a little bit as my spreadsheet is structured somewhat differently (see Sheet 3), but my results were less than desireable. Can you please have a look at the macro. I’ve used the data in Sheet 3 and the chart in Sheet 1, so I can take care of the linking problem as well. Basically the macro stops at the line Do While Cells(lngRow, 2) = 0. Plus I’m afraid that once the macro will work, it will populate the chart with wrong series (in rows, rather than in columns), but I could not test that as the chart is not updated. And finally, another question, if I have data in multiple columns, how can I add all the data (each in a different series) to my chart?

Thanks again,

Kislany

Reply | Quote

Here is an updated version.

Reply | Quote

Hans, this is great as well. Although I will be using for this project Steve’s idea, I already have a use for your macro, as I have a project where I have to dynamically add data to the chart with multiple elements. I will only need to modify it a little bit, but it should work Wow, I haven’t even though I can use this to my other project, but it will work!

Thanks again, you are all great!!
Kislany

Reply | Quote

Hans, how many weeks can you preview in your code? I changed the 6 in the appropriate line and could get 9 weeks, but could not get 10. A run time error 1004 appears. Is there a limitation to the number of weeks you could preview? I realize the chart could get nasty, but I would like to be able to see a quarterly chart if possible.

Thanks

Reply | Quote

The number of weeks can be larger than 10, but my code doesn’t check whether there are enough data columns. The version below takes that into account:

Sub UpdateChart()
Dim lngCol As Long
Dim lngStart As Long
Const lngNumCols = 15
lngCol = Cells(2, 256).End(xlToLeft).Column
Do While Cells(2, lngCol) = 0 And lngCol > 1
lngCol = lngCol – 1
Loop
lngStart = lngCol – lngNumCols + 1
If lngStart < 1 Then
lngStart = 1
End If
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Range(Cells(1, lngStart), Cells(2, lngCol))
End Sub

Reply | Quote

Thanks. I notice in this new code there is no reference to rows or sheet 3. Is this no longer necessary? I have tried the code and get a message about “UpdateChartcol cannot be found.

Reply | Quote

Your previous post was a reply to post 546,481 so I assumed that you were asking a question about the workbook attached to that post.
However, it’s not difficult to modify the code I posted. It’s a good excercise!

Reply | Quote

(Edited)
I did not see Hans’ answer before.
This is obviously a good solution using code.
(end of edit)

Hi Kislany,

If you have a more complicated structure, it might be easier to do something by macro, but for this more details need to be known.
Did you ever try to record a macro when doing the changes manually. This might give you a good idea how to proceed.

To answer your question about the lookup of text, please see the attached (not optimized) example.

Best regards

Wolf

Reply | Quote