X axis values not updating correctly

[Martin]

I have an excel spreadsheet for charting share prices of the FTSE 100.

The first worksheet stores closing prices of all the FTSE 100 shares
from May last year. Each day I paste & copy the closing prices into
this sheet.

Sheet 2 consists of a pivot table that resorts the data from sheet 1
with Company names listed along the top of the worksheet as column
headings and dates listed down the left hand side of the sheet. So,
with every new days closing prices a new row gets added to the bottom
of the pivot table.

Next, I have a sheet for each Company that lists the latest 70 dates
and their associated closing prices. The date information is imported
into each sheet by way of the following formula:

=VLOOKUP(MAX(OFFSET(HistoricData!$A$1,1,0,COUNTA(HistoricData!$A:$A)-1,1)),HistoricDate,1,FALSE)

Where 'HistoricData' is a name space that represents the pivot table
data containing the closing prices summary.

The data in each worksheet for each Company updates perfectly.

I have then created a line graph in each worksheet to show the
movement of the closing prices over time. The line graph updates
correctly upon each day's addition of closing prices, apart from the
dates on the X axis which seem to stubbornly stay at their original
values. This is also the case when you hover the mouse over a point on
the line graph - it displays the correct closing price as per the data
in the sheet but the incorrect date.

I have tried setting the X axis property set to Automatic and Category
in the Chart options but all this does is change the scale of the X
axis not the date values displayed.

Can anyone help me with this problem?

 

[Jon Peltier]

Are you also updating the x values data the way you are with the y
values? What do your series formulas point to for the range containing
the X values? (Or do Chart menu, Source Data, Series tab, and check out
the X Values or Category Labels).

- Jon

 

[Martin]

What a dipstick I am!!

Thanks for pointing me to the obvious! My X value range was
incorrectly set in relation to my Y data range. I.e. I had more X
values than Y values, so the dates on the X axis were being 'offset'
against the Y values when graphed.

Martin.

 

[Jon Peltier]

Martin -

Here's a trick. Since X and Y ranges are usually the same size, I define
the X range, then my defined name for my Y is:

=OFFSET(myXvalues,0,1)

Since I don't specify a size, it picks a range the same size as the
reference range, myXvalues. And since there are often several Y ranges,
they get defined like this:

=OFFSET(myXvalues,0,1)
=OFFSET(myXvalues,0,2)
=OFFSET(myXvalues,0,3)
=OFFSET(myXvalues,0,4)

- Jon

 

[Martin]

Thanks for the tip!

Martin.

Martin -

Here's a trick. Since X and Y ranges are usually the same size, I define
the X range, then my defined name for my Y is:

=OFFSET(myXvalues,0,1)

Since I don't specify a size, it picks a range the same size as the
reference range, myXvalues. And since there are often several Y ranges,
they get defined like this:

=OFFSET(myXvalues,0,1)
=OFFSET(myXvalues,0,2)
=OFFSET(myXvalues,0,3)
=OFFSET(myXvalues,0,4)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______