+ Reply to Thread
Results 1 to 11 of 11

XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pulled

  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pulled

    Hello there,

    I'm hoping one of you can help me with the following problem I have with making xy scatter plots and their source 'table' data.

    I have a workbook which contains results for each associated name and week (an example work book attached here: xy scatter for dynamic data.xlsx ).
    -The names are dynamic and the weeks are dynamic.

    I am working on having a single xy-scatter plot (and it's associated 'table') which will change as different names are selected from a data validation scroll down cell.

    I am familiar with using dynamic named ranges but I can't wrap my head around how to formulate such a range when I need to pull data for names that are dynamic with weeks that are also dynamic and when all of this is in the same worksheet.

    Do I need to use some combination of IF statments with dynamic named ranges? Or is this some type of conditional dynamic named range?

    Any advice on how I can set up my xy-scatter plot to do this?

    Thanks in advance,

    Geoff.
    Last edited by Geoff.; 09-15-2014 at 06:32 PM. Reason: added some information

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    A pivot chart is an easier solution BUT it might not fit your needs.
    1) Pivot charts can't use scatter plots for some reason, so you would need to use something else (e.g. line with points)
    2) The data would be in columns instead of rows as you show in your example
    3) The axis will size to the dates, you can't hard code it.

    Try it out and see if it meets your needs.
    Highlight your data and select insert pivot chart
    Drag Name into the 'Legend Fields'
    Drag Week into the 'Axis Fields'
    Drag Total into the 'Values'

    Now, with the drop down in the graph, which should be at the top of the legend and say 'Name', you can choose the Names you want.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    Thanks for your reply Pauley.

    I tried what you suggested (using a pivot chart) but I am having trouble integrating the pivot chart to my current dashboard set up, where all the tables and charts are are linked to one data validation cell(in which I can select a single name and the rest of the charts and tables will then refer to that name). Using the pivot chart, I get a chart that is pretty close to what I need but I don't know how to integrate the data validation method I am using to select a name with the filter that the pivot chart/table uses.

    Any idea? I've looked around the web and I can't seem to find antyhing.

    Thanks,

    Geoff.

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    No real easy way to have the pivot chart selection tie to another cell, that I'm aware of. Could probably do it with some VBA and tie it to a change in your selection cell, but it is usually best to avoid the worksheet_change event. Should be able to do this dynamic ranges, like you say, but I haven't put much thought into that since the pivot chart seemed easier. maybe tomorrow...

  5. #5
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    Thank Pauley. I'll keep looking into it. I would have loved to have been able to use pivot charts and tables for all the data but the way it comes to me does not lend itself well to pivot tables..

    Have a good evening,

    Geoff.

  6. #6
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    Highlight cells B16:I16 and type in this formula:
    =TRANSPOSE(OFFSET(Data!$A$1,MATCH($A$17,Data!$A$2:$A$15,0),1,COUNTIF(Data!$A$2:$A$15,$A$17),1))
    enter it with Control-Shift-Enter

    Highlight cell B17:I17 and type in this formula:
    =TRANSPOSE(OFFSET(Data!$A$1,MATCH($A$17,Data!$A$2:$A$15,0),2,COUNTIF(Data!$A$2:$A$15,$A$17),1))
    enter it with Control-Shift-Enter

    That should cause the chart to match the name selected in the dropdown in cell A17 (note, you seem to have the spreadsheet in manual update mode, so you won't see the changes until you updated the sheet).

  7. #7
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    Hi Pauley,

    Thanks for those formula, I think I see what they are doing but I when I drag them accross the dates and corresponding values do not change, they are all the same as in cells B16 and B17 (and yes, I refreshed the formula [f9 and shift f9]).

    I'm guessing some "$" need to be removed but I can't tell which. At a glance, can you tell which ones? Or did I do something wrong??

    I attached the file with the formula applied here: xy scatter for dynamic data formula applied.xlsx

    Thanks again,

    Geoff.

  8. #8
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    My guess is that you didn't follow the directions and typed in the formula in B16 and then dragged. You need to highlight the cells first. Then type in the formula. Then hit ctrl-shift-enter. Let me know if that works or not.

  9. #9
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    Hi Pauleyb,

    Apologies for the delay in getting back to you to confirm that your code worked as planned. Thank you

    You were right, I did drag. I didn't realize it was possible to input formulas in the way you had described. I don't understand how that worked. If you can, please let me know, if not, you have already done plenty.

    Thanks again,

    Goeff.

  10. #10
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    It is a nuance, but let me try...

    If the array formula outputs an array, then you need to highlight the cells where you want the array to appear, then type in the formula. Formulas like TRANSPOSE, LINEST, MMULT output arrays.

    If the array formula outputs a single answer, then you can type it in a cell and generally drag it so that the variables adjust from the dragging.

    If you look at my formulae, you can see I used absolute addressing for every variable (i.e. the $ signs). When you drag that, the variables will not change, so it just copied the same formula over and over, and, for whatever reason, Excel assumes you want the first element of its output array to always be shown.

    Excel treats these methods differently internally, too. You can see this if you try to delete one of the cells or try to add a cell between the answers. Try it. Using this formula try dragging it, and then delete a cell in the middle. Then highlight the cells and type in the formula and then try do delete a cell in the middle.

    Thanks for the rep bump.

  11. #11
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pul

    Thanks for explaining that Pauleyb, much app!

    I haven't fully grasped the way excel handles arrays but your explanation has definitely helped me

    Cheers,

    Goeff.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-05-2014, 06:46 PM
  2. Dynamic Range for Chart scatter plot
    By dpk1 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 04-30-2013, 03:29 AM
  3. [SOLVED] Dynamic Scatter Graph (Using the table method) - Data label of the last entry
    By gjrr4x1 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-28-2012, 07:47 AM
  4. [SOLVED] Dynamic Scatter Plot
    By blaxal in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-22-2012, 08:12 AM
  5. How to retain previous data pulled from a dynamic worksheet
    By GTurley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-13-2008, 10:08 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1