Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Automatic, multiple charts using first column as X-axis

  1. #1

    Unhappy Automatic, multiple charts using first column as X-axis



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi. I'm having big problems with something that might be very easy to do, although I tried (and searched how to do it) without success:

    I've got a sheet, with 'Date/Time' as first column.
    I can select it, along with any other column to insert a graphic chart into the worksheet and works fine.
    My question is.... how can I make Excel generate automatically ONE CHART for each column, with 'Date/Time' as X axis? I tried some dynamic table/charts but it's incredibly messy (it's faster for me to create the charts one by one, manually!).


    Thanks for your help.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    I'm not sure I quite follow you here. Can you upload a workbook with some sample data and give us a bit more of an idea of what you're expecting to see? If you have a chart created to begin with, that's even better, as it saves us guessing.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Is the attached the sort of thing you mean.
    Use the dropdown in sheet Graphs to select the range of data that you want to see graphed against the fixed range of data.

    It is all down using Dynamic Named ranges for the data series, which will grow as you add more data on sheet Data.

    As you change the value in cell G2 of Graph, it changes the value in cell H3 with a simple Match formula
    =MATCH(G2,myList,0)-1

    which provides the offset to the data range chosen
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  4. #4
    Quote Originally Posted by Roger Govier View Post
    Hi

    Is the attached the sort of thing you mean.
    Use the dropdown in sheet Graphs to select the range of data that you want to see graphed against the fixed range of data.

    It is all down using Dynamic Named ranges for the data series, which will grow as you add more data on sheet Data.

    As you change the value in cell G2 of Graph, it changes the value in cell H3 with a simple Match formula
    =MATCH(G2,myList,0)-1

    which provides the offset to the data range chosen
    Thanks, this could do the trick! How do you create a combo graph like this?

  5. #5
    Thanks to Héctor Miguel from answers.microsoft.com , who helped a lot with the issue.
    This macro does exactly what I wanted, although I'm interested in your combo method too, please tell me how to do it.

    Thanks!

    Code:
    Sub Crear_Graficas_x_Columna()
      Dim n As Byte
      With Range("a1").CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1)
          For n = 2 To .Columns.Count
            Union(.Columns(1), .Columns(n)).Select
            SendKeys "{tab " & 5 + (Val(Application.Version) > 11) & "} "
            Application.CommandBars.FindControl(ID:=436).Execu  te
            ActiveChart.SeriesCollection(1).Name = .Columns(n).Offset(-1).Resize(1)
            ActiveChart.ChartTitle.Text = .Columns(1).Offset(-1).Resize(1)
            ActiveChart.ChartType = xlLine ' This stands for 2D line graph,  there are multiple styles to choose. You can use interactive mode by  using "Application.Dialogs(xlDialogChartType).Show" instead.
          Next: End With: End With
      With ActiveSheet
        For n = 1 To .ChartObjects.Count
          .ChartObjects(n).Top = .Rows(16 + (n - 1) * 2).Top
          .ChartObjects(n).Left = .Columns(2 + n - 1).Left
        Next: End With
    End Sub

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by Fr3dY View Post
    Thanks to Héctor Miguel from answers.microsoft.com , who helped a lot with the issue.
    Hi Fr3dY,

    In future, if you're posting your issue in multiple places, please provide a link (at both places). It's a little frustrating for people who work on issues with people to find out that someone else is also working on the issue elsewhere.

    Please have a review of our multi-forum posting policy to understand why this is an issue.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Quote Originally Posted by Ken Puls View Post
    Hi Fr3dY,

    In future, if you're posting your issue in multiple places, please provide a link (at both places). It's a little frustrating for people who work on issues with people to find out that someone else is also working on the issue elsewhere.

    Please have a review of our multi-forum posting policy to understand why this is an issue.
    OK, agreed. I wrote the solution once achieved, just to avoid people lose their time.

    Thanks.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by Fr3dY View Post
    I wrote the solution once achieved, just to avoid people lose their time.
    Believe me, I very much appreciate it. It's nice to see the solution posted, even if it's not provided here.

    I wanted to share that with you though, as many of our experts post on multiple boards. They'll come back and get everyone to work on it in one place if we know, which often leads to more complete solutions faster. It also avoids someone feeling like they've put in a bunch of time for nothing too. If you keep that in mind in future, you'll build some great relationships with world class experts, and get your questions answered faster.

    All the best,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    I have a similar problem, but I'm trying to form multiple charts based on the status of one column. In this case, I have a column with Date, a column for the Machine and a column with Quantity. I would like to form separate charts with Date along the x-axis and Quantity along the y-axis for each Machine. Thanks in advance. Todd

  10. #10
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Post a sample of your data, and what you want to see.
    It's much easier than us having to try and build something which doesn't match your circumstance.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Page 1 of 2 1 2 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
  •