## Cell reference in chart scale?

MS EXCEL

### Next

• 1. Can a line(s) be added to a stacked-clustered chart?
I followed the directions for creating a stacked-clustered chart, but I lost the lines that I also had on the chart. Is there a way to represent data as a line in a stacked-clustered chart?
• 2. Weeknum Year by Year Compare
I have three years' worth of sales data, by transaction. Example: 1/2/2002 5 1/2/2002 2 1/2/2002 3 1/3/2002 4 1/6/2002 2 And so on. What I would LIKE would be a chart showing: WEEK 2002 2003 2004 1 14 xx yy 2 2 xx2 yy2 3 and so on. I know WEEKNUM returns the week number, but it shows the same value ("1") for 1/2/2002, 1/2/2003, etc. So how do I combine the data to give me the week of each year, and then lay it over a table?????? This is what you get for being a Lit Major!
• 3. Can I use side by side colums and stacked columns in same graph?
I have a completed graph that shows side by side columns and a line, but I would like to add a stacked column to the graph without losing the other side by side columns.
• 4. Multiple rows of data on a single axis (charting)
I have several rows of data that are separated by several other rows (that I don't want to chart). Is there a way to chart several rows of data on one continuous axis? For example (see below) I want to chart Row 1, Row 3 and Row 5 on one axis that would show a trend of 25, 50, 75, 5, 10, 15, 1, 2, 3. I have tried separting the rows with comas, colons, &, "", + and can't get it to work. I don't want to copy and paste the data into a new format b/c that will take too much time. Any suggestions? Column A Column B Column C Row 1 25 50 75 Row 2 10 20 30 Row 3 5 10 15 Row 4 2 4 6 Row 5 1 2 3
• 5. Problem with xlusrgal.xls file
I'm using Windows 2000 and Excel 97. During X'mas I started learning charting - J. Walkenbach's Excel Charts (2003). Got stuck after Chapter 2, after I had entered the user-defined data (parameters) and save them in the xlusrgal.xls file. When doing Chapter 3 exercise (plotting other charts using new data), I was stuck with my previously user-defined instructions. Did try to locate and erase that file but in vain as I can't find it in any folder - appears to be a hidden somewhere. Two questions: (a) How can I free myself by locating the file and deleting/changing the parameters? (b) Can I create several such user-defined function files, each one for a different chart type and how to do it? I'm an experienced Word user but a beginner in Excel. Please help - in elementary terms pl.

### Cell reference in chart scale?

```Can I use a cell reference, rather than absolute value, to set min or max
Y-axis scale in chart?  I think I have done this before but cannnot remember
how.  Thanks.
```

### Re: Cell reference in chart scale?

```Hi,

Not directly. You would need to use event code to capture cell changing
and then apply value to chart.

Jon Peltier has details.
http://www.**--****.com/

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.**--****.com/
```

```Jon,
the duplicate Sheets("XY") was indeed very silly! I took it out and the
debugger still strike that line.
The error I get is: Run-time error 438.
I change that line to ///
Windows("erslOg_XxYy.XLS").Sheets("XY").Range("\$e\$41").Value    ////and
the probem persists...
It will be nice if I get it fixed since then I can create a kind of
zoom in the chart (using a second range of vaues for axes).

The working code I took it from your website and I like to thank you
very much for that,
Emil

```

```Can anyone help me with this macro (my programming experience is very
limited).
I have a chart Chart4 on a sheet XY in the workbook erslOg_XxYx.
The same chart is in a separate sheet Chart2 on the same workbook (I
did that so I can print it easier).
The scale and the title is changed from reference cells from XY.
For the chart on the XY sheet is working fine, but is not for the chart
on separate sheet.
Last night I had the impression is working but now it is striking at:
.HasTitle = True
if I comment that it will execute (will change the title accordingly)
but it will strike at any row which makes reference to the cells in XY
sheet like:
.MinimumScale =
ThisWorkbook.Sheets("XY").Sheets("XY").Range("\$e\$41").Value

What is wrong with addressing those cells?
Thank you,
emil

-----------------------------------------------this is
working---------------------------------------------------------
Sub scales2()
' change scales on chart on the current sheet Macro

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Range("\$c\$1").Text
End With

With ActiveChart.Axes(xlCategory)
.MinimumScale = ActiveSheet.Range("\$e\$41").Value
.MaximumScale = ActiveSheet.Range("\$e\$42").Value
.MinorUnit = ActiveSheet.Range("\$e\$43").Value
.MajorUnit = ActiveSheet.Range("\$e\$44").Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range("\$e\$41").Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = ActiveSheet.Range("\$h\$42").Value
.MaximumScale = ActiveSheet.Range("\$h\$41").Value
.MinorUnit = ActiveSheet.Range("\$h\$43").Value
.MajorUnit = ActiveSheet.Range("\$h\$44").Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range("\$h\$42").Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
------------------------------------------------------this is not
working-------------------------------------------------

' change scales to the same but for chart on separate sheet Macro

ActiveWindow.Visible = False

Windows("erslOg_XxYy.XLS").Activate
Sheets("Chart2").Select

ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text =
ThisWorkbook.Sheets("XY").Range("\$c\$1").Text
End With

With ActiveChart.Axes(xlCategory)
.MinimumScale =
ThisWorkbook.Sheets("XY").Sheets("XY").Range("\$e\$41").Value
.MaximumScale = ThisWorkbook.Sheets("XY").Range("\$e\$42").Value
.MinorUnit = ThisWorkbook.Sheets("XY").Range("\$e\$43").Value
.MajorUnit = ThisWorkbook.Sheets("XY").Range("\$e\$44").Value
.Crosses = xlCustom
.CrossesAt = ThisWorkbook.Sheets("XY").Range("\$e\$41").Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = ThisWorkbook.Sheets("XY").Range("\$h\$42").Value
.MaximumScale = ThisWorkbook.Sheets("XY").Range("\$h\$41").Value
.MinorUnit = ThisWorkbook.Sheets("XY").Range("\$h\$43").Value
.MajorUnit = ThisWorkbook.Sheets("XY").Range("\$h\$44").Value
.Crosses = xlCustom
.CrossesAt = ThisWorkbook.Sheets("XY").Range("\$h\$42").Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

End Sub

```

```Excel 2007 (or 2010 Beta, if that's the only way to do it)

I've developed a Gantt Chart using a stacked bar chart.  It works great.

Is there any way to have the horizontal axis (or vertical axis, I get
confused with the terms in a stacked bar chart) expand in and out based on
the MIN and MAX of the dates in the plotted range?
```

```I have a chart with the following data:

x    y
-    -
1    1
1.25    1.25
2    2
3    3
5    5

I expect to see a chart with a straight line graph.  The only way I
can get the X-axis to scale is to go into Chart Options->Axis-
>Category (X) axis->Time-Scale and then format the X-axis as a number
as opposed to a date.  However, this still treats my data as dates so
that 1 and 1.25 show up on the same point on the X-axis (1).  Is there
any way to "number" scale my X-axis or some other approach to
accomplish this goal of my X-axis scaling along with the values?

Thanks.

Edward

```

```I have a chart on a worksheet and want to make the Y scale maximum value
equal the value of cell Y3 (this is a merged cell range Y3:Y5), rounded up to
the next increment.  For example, 101% in merged cell Y5 would be 105% in
the chart Y scale maximum.  How would I do this?
```