Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort of) (

    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

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically changing series in a chart (sort of) (

    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 [img]/forums/images/smilies/sad.gif[/img] 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

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort o

    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*

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically changing series in a chart (sort o

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

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort o

    (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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamically changing series in a chart (sort of) (

    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

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort o

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically changing series in a chart (sort o

    Here is an updated version.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort o

    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

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort o

    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 [img]/forums/images/smilies/smile.gif[/img] 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

  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort of) (

    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(qTmpTotSelec t!$A:$A)-1,1)
    Added: =OFFSET(qTmpTotSelect!$B$2,0,0,COUNTA(qTmpTotSelec t!$B:$[img]/forums/images/smilies/cool.gif[/img]-1,1)
    Deleted:=OFFSET(qTmpTotSelect!$C$2,0,0,COUNTA(qTmp TotSelect!$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!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamically changing series in a chart (sort of) (

    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

  13. #13
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort of) (

    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 . . .

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamically changing series in a chart (sort of) (

    That also works. Notice that XL converts the "'sheetname'!Added" to "'Filename.xls'!Added" nomenclature...

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically changing series in a chart (sort of) (

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •