Line graph

Topic

New Reply

I am a complete novice when it comes to using charts. I’m trying to compile a line chart of 15 peoples (A-O) progress accross time of 6 dates. The amount of days between each plotted date is not relevant here.

I only want to see 6 dates on the category axis, and the plots should be an equal distance apart. Also I would like to flip the value axis so that 0 is at top and 180 is at bottom. I want the plotting to start from 171, not 0. I need the value in increments of 5.

Please could I get some pointers as to how to achieve this. Thanks

Reply | Quote

Replies

I am a complete novice when it comes to using charts. I’m trying to compile a line chart of 15 peoples (A-O) progress accross time of 6 dates. The amount of days between each plotted date is not relevant here.

I only want to see 6 dates on the category axis, and the plots should be an equal distance apart. Also I would like to flip the value axis so that 0 is at top and 180 is at bottom. I want the plotting to start from 171, not 0. I need the value in increments of 5.

Please could I get some pointers as to how to achieve this. Thanks

I am bit confused what exactly you are looking for as I generally dont use charts but I tried something. Hope this will help to get some idea.

Reply | Quote

Prasad didn’t explain how he modified the chart.

Click on the chart to select it.
Select Chart | Chart options…
Activate the Axes tab.
For the Category axis, select Category instead of Automatic (or Time scale).
Click OK.

Next, double click the value axis (or click on it, then select Format | Selected axis…)
Tick the check box “Values in reverse order”.
If you still want the category axis to be displayed at the bottom, tick the check box “Value (Y) axis crosses at maximum value”.
Set the Major unit to 5, and the Maximum to 175 (the first multiple of 5 above 171).
If you really want the axis to stop at 171, set Minimum to 0 and Maximum to 171.
Click OK.

Note: I don’t think the values in column B belong in the data values for the series, but that’s for you to decide.

See attached version.

Reply | Quote

Prasad didn’t explain how he modified the chart.

My mistake. Although I tried to adopt the same approach but I was not sure whether this will serve the purpose or not.

Reply | Quote

My mistake. Although I tried to adopt the same approach but I was not sure whether this will serve the purpose or not.

You did take the correct approach as far as I can tell!

Reply | Quote

You did take the correct approach as far as I can tell!

Thanks for encouragement.

Reply | Quote

Thanks, I’m not familiar with using any charts, but probably could and should a lot more. I have been dabbling around and now have it the way that I want.

Now I am trying to have it automatically update when the source data changes (expands), so I have a few questions:

In the series there are 15 people:

Name: =’Admin Chart’!$A$1:$B$1 TO =’Admin Chart’!$A$15:$B$15
Values: =’Admin Chart’!$H$1:$M$1 TO =’Admin Chart’!$H$15:$M$15
X axis labels: =’Admin Chart’!$H$17:$M$17

The values and X axis labels need to increase by an additional column following this:

amh.Range(“H1:H” & w).Copy Destination:=ach.Range(“N17”) This needs to be the first blank cell on row 17, how?

Once the data is pasted to the next available cell on row 17, there are lookup formulas in rows 1:15 of that column, which need to then be included in the chart.

so the source data needs to expand to:

Values: =’Admin Chart’!$H$1:$N$1 TO =’Admin Chart’!$H$15:$N$15
X axis labels: =’Admin Chart’!$H$17:$N$17

I’m hoping that this makes sense

Reply | Quote

Change

amh.Range(“H1:H” & w).Copy Destination:=ach.Range(“N17”)

to

amh.Range(“H1:H” & w).Copy Destination:=ach.Cells(17, ach.Columns.Count).End(xlToLeft).Offset(0, 1)

ach.Cells(17, ach.Columns.Count) is the last cell in row 17; .End(xlToLeft) finds the last non-blank cell and .Offset(0, 1) is one cell to the right, i.e. the first blank cell.

To make the chart expand (or contract) automatically, you need to define dynamic named ranges for the X values and for each of the series, and use those in the definition of the data series for the chart.

See the attached version.

Reply | Quote

To make the chart expand (or contract) automatically, you need to define dynamic named ranges for the X values and for each of the series, and use those in the definition of the data series for the chart.

See the attached version.

Sorry Hans, I don’t understand the steps that you have taken to set this up. Please would you mind explaining?
Also:
I can see that this works when new data is added to the right of the existing data. But I have formulas there that will return zero until the raw data is added below. So how do I have the chart ignore the blanks?

Reply | Quote

Select Insert | Name | Define… to see how the dynamic named ranges are defined.

Select the chart, then select Chart | Source Data and activate the Series tab. You’ll see how the dynamic named ranges are used in the definition of the series.

More info about dynamic ranges in How to create a dynamic defined range in an Excel worksheet. And about dynamic charts in Create a Dynamic Chart.

I’d remove the formulas that return zeroes or blanks if there are no data. You’re filling the worksheet using code anyway, so you can add the formulas when data are available.

Reply | Quote

Thanks Hans, I’m making slow progress….

In the attached version, what do I need to change to remove the blanks from the chart. I’m guessing that it is to do with the defined name, would you mind explaining the formula to me please?

Also, the next data addition will be to cell N17. At that point I will need to add a formula to cells N1:N15 >

=VLOOKUP($A1,$A$19:$M$189,13,0) – looking at cells A1:A15 respectively.

Following that will be O17, still looking at A1:A15 but column 14 and so forth.

How can I achieve this please?

Reply | Quote

If you remove the “x”s from A17:G17, the chart will be OK.

If you add data to N1:N15 and N17, the chart will adjust itself automatically.

Reply | Quote

If you add data to N1:N15 and N17, the chart will adjust itself automatically.

I have sorted out the adding of data to N17 and below as thats a simple copy & paste. I still can’t work out how to get the vlookup formulas into N1:N15 (and corresponding columns) that feed the chart, as per my previous post?

Reply | Quote

I still can’t work out how to get the vlookup formulas into N1:N15?

By entering them, either manually or through code, perhaps?

I’m sorry, I don’t really understand your problem, but it’s bedtime for me now.

Reply | Quote

OK, I think I’m close. Would you mind taking a look at the code in the attached file which is practically complete apart from the incorrect syntax for adding the VLookup formulas to the range. Hopefully the comments in the code will better explain what I am attempting to achieve. The formulas that I need in column N will be the same as are in column M apart from they will need to look to column 14 instead of 13. Hope this is clear.

Thanks for your help, and enyoy your well deserved nap!

Reply | Quote

Try this version, it’s slightly simpler than what you tried to do:

Code:

Sub test()
  Dim amh As Worksheet
  Dim ach As Worksheet
  Dim w As Long
  Dim c As Long
  Dim oCell As Range

  Set amh = Worksheets("Admin History")
  Set ach = Worksheets("Admin Chart")

  w = amh.Cells(Rows.Count, 8).End(xlUp).Row

  ' First available cell in row 17
  Set oCell = ach.Cells(17, ach.Columns.Count).End(xlToLeft).Offset(0, 1)
  ' Get the column number
  c = oCell.Column

  ' Add the latest data to the next available column in Admin chart
  ' from Admin Manager History for the purpose of lookup
  amh.Range("H1:H" & w).Copy Destination:=oCell

  ' Add VLOOKUP formulas in rows 1-15
  ach.Range(ach.Cells(1, c), ach.Cells(15, c)).FormulaR1C1 = _
	"=VLOOKUP(RC1,R19C1:R189C" & c & "," & c & ",0)"
End Sub

Reply | Quote

Thanks a lot Hans!!

Reply | Quote

Hans, a further question on this if I may. I need to add a few further analysis columns which is causing the chart to corrupt. If you select columns G:I and insert 3 blank columns, what else do I need to then do to correct it please?

(and I promise that will be it…. )

Reply | Quote

Inserting the columns messes up the VLOOKUP formulas: the cell references are adjusted automatically, but the column indexes aren’t. For example, the formula in K1 (after inserting the columns) is

=VLOOKUP($A1,$A$19:$K$189,8,0)

The column index 8 refers to column H, which was correct before the insertion, but not any more. You’ll have to change it to

=VLOOKUP($A1,$A$19:$K$189,11,0)

or perhaps even better to

=VLOOKUP($A1,$A$19:K$189,COLUMN(),0)

because that will make the formula adjust itself correctly if further columns are inserted (or deleted). And you can fill down this formula to K15, then to the right.

You can also implement the latter in the code: change

Code:

  ach.Range(ach.Cells(1, c), ach.Cells(15, c)).FormulaR1C1 = _
	"=VLOOKUP(RC1,R19C1:R189C" & c & "," & c & ",0)"

to

Code:

  ach.Range(ach.Cells(1, c), ach.Cells(15, c)).FormulaR1C1 = _
	"=VLOOKUP(RC1,R19C1:R189C" & c & ",COLUMN(),0)"

Reply | Quote

Thanks Hans, wish I had read this an hour ago, after spending time trying to figure it out, I eventually realised that it was the formulas not the graph.

I have a nasty habit of implementing something and later deciding on changes, causing more work. I must think more from the outset!!

Thanks very much for your help, I’m not changing it any more!!

Reply | Quote