Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Interactive Chart (2003)

    How can I have one of the chart changing when I select a specific line? I mean how can I connect the data series with the current selection in the sheet.
    Thanks and regards,
    Marcel

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

    Re: Interactive Chart (2003)

    Can you explain in more detail what you want to accomplish? (Most of the charts in the workbook you attached have one data series, but some have more than one.)

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

    Re: Interactive Chart (2003)

    Hans,
    By selecting a cell or a range of cells in the sheet, I would like to see the change on chart data. As an exemple I have add one sheet in the attachment. The first sheet is for the sales data and the second sheet is for the cost of sales data. How can I do it with only one chart?
    Hoping that my explanation is clear enough
    Thank you
    Marcel

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

    Re: Interactive Chart (2003)

    Sorry, I don't have the slightest idea what you mean. Can you try to give a detailed example of what you want?

  5. #5
    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: Interactive Chart (2003)

    Does this do what you want?

    I created a chart sheet, you can just select the item from the list that you want to plot.

    Steve

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

    Re: Interactive Chart (2003)

    Or this? Click on a cell in column A to change the chart.

  7. #7
    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: Interactive Chart (2003)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Nice simple code.

    I was trying to give what I think was needed, without using any code. Though yours seems to be what was asked for...

    Steve

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

    Re: Interactive Chart (2003)

    Thank you. I just made a guess, I don't know why some of the charts in the sample worksheet have multiple series, and if that is relevant.

  9. #9
    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: Interactive Chart (2003)

    I don't know either.

    As I know you are aware, but some ideas for Marcel:
    My scheme could have multiple comboboxes so that one could chose to have multiple items to plot (a "none" would have to be a choice) so some "revampling of the list would be required. It still could be done without any code.

    If coding is allowed a list box with multi-select could be used to plot more than 1.

    I would imagine a macro just based on selecting multiple items from col a could be created to create the chart, though it would be more complicated than your current one (I imagine deleting all the current ranges, then creating all new ones, sourcedata is not good (if i recall) for non-contiguous ranges)

    It would depend on the needs...

    Steve

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

    Re: Interactive Chart (2003)

    This is what I was wanted.
    Thank and best regards,
    Marcel

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

    Re: Interactive Chart (2003)

    Steve,
    This is also an interesting option. Would you explain how the change in the dropdown list change the source data for the chart?
    Thank you
    Marcel

  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: Interactive Chart (2003)

    <hr>Would you explain how the change in the dropdown list change the source data for the chart?<hr>

    It really doesn't. If you look at the chart you will see the source data is:
    =SERIES(Sheet1!$A$2,Sheet1!$B$4:$F$4,Sheet1!$B$2:$ F$2,1)

    This does not change. The X-data is B4:F4 the label is A2 and the Y-range is B2:F2.

    What the combobox does is:
    It reads from Sheet1!$A$5:$A$34 ("Input range") which is your list of items
    When you make a selection from the box it puts the "index" of the list (item 1, 2,3,etc) in the cell Sheet1!$A$1 ("cell Link")

    The values in B2:F2 have a formula using INDEX which reads the info from the selected "row".

    Thus the combobox changes the values in B2:F2 (via formulas, no macros) and this change in the source data values (not a change in the source data itself) is what is reflected in the chart.

    If the list is too long, you could also modify this approach to select "sublists" (have one to select whether to choose from the 1st, 2nd or 3rd list) and then the other which only lists the items from the selected sublist. same principle: use index to get the values, plot the same source data.

    Steve

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

    Re: Interactive Chart (2003)

    Very good. Now I have the options to add several series and change them with several dropdown list.
    Thank you very much for your help.
    marcel

Posting Permissions

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