Using a cell reference as the axis value for Horizontal axis crosses



  • 1. How To: Link worksheet data to pre-existing chart tables
    I have a powerpoint presentation which has all the required excel data charts in it already. Currently to update the charts I need to pick the appropriate data out of seperate excel worksheets and manually input the data to the appropriate charts that generate the charts. How do I link the worksheets with the supporting data directly to the appropriate cells in the excel tables that are used to generate the charts. I would like it so that when new data is entered on the excel worksheets, the appropriate cell data is transferred to the excel tables in the powerpoint lecture that generate the chart stats. Confused??? well so am I, so any advice or insight would be greatly appreciated on how I go about automating this process so that when I go to give the powerpoint lecture, I automatically have the latest data being presented on the charts.
  • 2. Excel won't let me uncheck Size with Window
    I have tried both on the View menu and in Tools/Options/charts menu. I have also tried using VBA ActiveChart.SizeWithWindow = False but the property is still TRUE. Is there something other setting the forces this TRUE?
  • 3. how to convert lotus 1 2 3 data and graphs to excel 2000.
    I've tried 'save as' option and 'open, file' option. The data is converted but the 30 odd graphs are not. Regards Martin
  • 4. Excel: Can I change Chart-title/category/value from a sheet-ce
    For the "Name of a Serie", it's working correctly, referring to "=Sheet1!A1". But for "Chart-title", "Value X" and "Value Y", your reference to "=Sheet1!A1" is not working. Chart-title will be then "=Sheet1!A1". I hope a newer version of Excel will work in the way you describe. Thanks in advance. "AAA" wrote: > It is eay to make an example to do this. You just nee to select the chart > title and then input an formula such as "=Sheet1!A1" in the formulabar. > > >

Using a cell reference as the axis value for Horizontal axis crosses

Postby betuttle52 » Thu, 24 Jan 2008 04:50:53 GMT

Is there any way of referencing a cell as the value in the "axis
value" area of the "Format Axis dialog box. I am currently entering a
number i.e. 2.5. I would like to be able to have this number change
when a user enterer a value in a cell?  Would it have to be VBA code
or is there another way?

Re: Using a cell reference as the axis value for Horizontal axis crosses

Postby Andy Pope » Thu, 24 Jan 2008 19:40:31 GMT


A coded solution is currently the only way.



Andy Pope, Microsoft MVP - Excel

Re: Using a cell reference as the axis value for Horizontal axis crosses

Postby betuttle52 » Fri, 25 Jan 2008 03:25:13 GMT

Great I have 9 charts on a sheet with different values in each
horizontal access crosses at. Thanks for your response

Re: Using a cell reference as the axis value for Horizontal axis crosses

Postby Breck » Sat, 26 Jan 2008 22:40:30 GMT

I know that it has been only three days since I posted this but any takers 
on answering this.

Re: Using a cell reference as the axis value for Horizontal axis crosses

Postby Andy Pope » Sat, 26 Jan 2008 22:59:58 GMT


I answered you by confirming VBA was the only way.
If you need actual code see Jon's page.


Anything else and you need to spell it out.


Andy Pope, Microsoft MVP - Excel

Similar Threads:

1.Changing a horizontal axis crosses value using VBA

I have just started trying to learn VBA since the 1st of the year. I
used macro recorder to record changes in the "Format Axis" "Axis
Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt"
portion of a chart. Because I wanted to be able to enter a crosses at
value into a cell I added the = Range(). The following code is working
except there is a short wait while the code is executing.

 ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("I21")
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("J21")
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("K21")
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("L21")
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("M21")
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("N21")
    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("O21")
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("P21")
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.Axes(xlValue).CrossesAt = Range("Q21")

Here is what I'm trying to do now. If the value in the chart is the
same as in the cell I want to skip the code for that chart. So if the
value in the cell is different need to run. I can't figure out how to
compare the 2 values. This is what I have attempted without sucess.

  ActiveSheet.ChartObjects("Chart 1").Activate
        If Not ActiveChart.Axes(xlValue).CrossesAt.Value =
Range("I20") Then
            ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20")
        End If

I sure it something very simple and basic but I'm not sure what to do.
Thanks in advance

2.Need PivotChart x axis to cross Y axis at minimum Y axis value

I see an option to have x cross y at max y, but how can I say min y?

3.Value (Y) axis crosses between categories and Secondary Value (Y) Axis

Hi all -

I have 7 series on my chart
Series 1 is column chart - may be area chart eventually (2007 data)
Series 2 is column chart 2008 data
Series 3 is column chart 2007 avg
Series 4 is a column chart 2008 avg
Series 5 is a line chart (32,0 benchmark)
Series 6 is a line chart (points to column chart for 2007 avg, I then
add text to right to explain the column)
Series 7 is a line chart (points to column chart for 2008 avg, I then
add text to right to explain the column)

I would like the column charts for 2007 and 2008 data to be flush to
the origin (12 data points each year)
I would like the benchmark line chart of 32 to span the chart area
from left to right (16 data points)

>From what I've read, the way to accomplish this, is to clear the tick
mark from Value (Y) axis crosses between categories when formatting
the x axis.

However, when I do that, the data point for 2007 closest to the origin
appears cut-off on left side of column

I more or less posted this information on Ozgrid a few days ago
(Ozgrid is now down until Nov. 13 according to the site). Andy Pope
replied that if I move the line charts to the Secondary Value (Y)
Axis, that would resolve the problem.

I must be doing something wrong. I did assign the line chart to
Secondary Value (Y) Axis and then cleared the tick mark on the x axis:
Value (Y) axis crosses between categories. However whatever datapoint
is closest to the Primary Y Axis disappears entirely

What am I doing wrong here?
Send a confidential e-mail for a sample copy of the workbook
Best regards,

4.Can't get the x-axis to cross at value 0 for both y-axes

Hello - 

I am working on an Excel chart with both a primary and secondary y-axis and 
only one x-axis.  The primary has positive and negative values.  The 
secondary has all positive values.  I am using the secondary as a rank (by 
selecting the box to invert values so the highest rank has a value of 1 and 
appears at the top of the secondary y-axis, where rank 25 is the lowest 
possible rank and appears at the bottom of the secondary y-axis), in theory 
where the x and y axes meet.  The x-axis is different points in time.  So 
this chart shows trends over time.

The issue I am having is the primary x-axis crosses the primary y-axis at 0 
(y-axis value), which is what I want.  However, the primary x-axis crosses 
the secondary y-axis at a value that is not the lowest value on the secondary 
y-axis.  In this case, since I am using it as a rank, it is not crossing at 
the lowest rank.  I am not showing the secondary x-axis associated with rank.

I don't have this issue if all of the values on the primary y-axis are 
positive.  In other words, the primary x-axis crosses at 0 on the primary 
y-axis and at the lowest rank on the secondary axis.

My question is, is there a way to fix my problem?  Is there an option where 
I can set the value where the primary x-axis crosses a value on the secondary 
y-axis?  I know I can set the value for Primary x-axis to primary y-axis - 
and do the same for the secondary x and y axes, but I'm not aware of a way to 
cross this primary - secondary boundary.

I imagine the graph's axes looking like a backwards 4 - the primary y-axis 
goes above and below the primary x-axis.  The primary x-axis is approximately 
in the middle.  The secondary y-axis is as high as the primary y-axis but 
only goes down to the x-axis (i.e., it does not extend below the x-axis).  Is 
this possible?

Something like this:

|            |


- Derek

5.X-axis to cross Y-axis at avrg Y-value

Have been trying for ages to get the X-axis to cross the Y-axis at exactly 
the avrg Y-value - to see results above and below average. But I can't find a 
way to do it. Anyone?
(Tried recording a macro that takes the avrg value and pastes it into the 
field in the "Value (X) axis crosses at" field, but it pastes the formula 
rather than the number, and Excel can't handle a formula in that field...)

Thanks for any help

6. 2-axis problem with horizontal crossing

7. How do I reference x-axis or y-axis scale values to a worksheet ce

8. why can't Y-axis cross the X-axis at zero

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 9 guest