Automatic, multiple charts using first column as X-axis

[Fr3dY]

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.

[Ken Puls]

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.

[Roger Govier]

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

[Fr3dY]

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?

[Fr3dY]

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!

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

[Ken Puls]

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.

[Fr3dY]

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.

[Ken Puls]

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,

[tmartcw]

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

[Roger Govier]

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.