I can't tell what's wrong. Nominally it looks okay.
To make things easy, I typically only define one name in very much detail,
then base the others on that one.
Time
=OFFSET(Sheet1!$A$1,SCROLLVALUE,0,ZOOMVALUE,1)
Open
=OFFSET(Time,0,1)
High
=OFFSET(Time,0,2)
Low
=OFFSET(Time,0,3)
Close
=OFFSET(Time,0,4)
Test to make sure Time represents an actual range. In the Define Names
dialog, click on the name Time in the list, then click in the refers to
formula, and the range should be highlighted by the marching ants outline.
If it isn't, make sure Zoomvalue is greater than zero.
From here it's a straightforward procedure to assign names to X values and Y
values. It sounds like you've done this before. I've just tested it in 2007,
and it's really just the same as in earlier versions.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
wrote in message
...
I am currently doing data manipulation for my series of data. I have
Time, Open, High, Low, Close. There are approximately 6000 rows of
data that I need to input into the Candle Chart and to do that I
require a Scroll and Zoom function. I have been using a template for
Scroll/Zoom for a normal basic Line Chart and attempted to reconfigure
for a Candle Chart. I have named the following:
SCROLLVALUE=Sheet1!$S$31
SERIES1==OFFSET(Sheet1!$B$1,SCROLLVALUE,0,ZOOMVALU E,1)
SERIES2==OFFSET(Sheet1!$C$1,SCROLLVALUE,0,ZOOMVALU E,1)
SERIES3==OFFSET(Sheet1!$D$1,SCROLLVALUE,0,ZOOMVALU E,1)
SERIES4==OFFSET(Sheet1!$E$1,SCROLLVALUE,0,ZOOMVALU E,1)
TIME==OFFSET(SCROLL_TESTER.xls!SERIES1,0,-1)
ZOOMVALUE==Sheet2!$S$29
Then I go into 'Select the Data Source' in the actual Candle Chart and
try to enter the following details for the X-axis (time) and Series 1,
Series 2, Series 3, Series 4 (all required to draw a Candle Chart in
Excel):
='SCROLL_TESTER.XLSX'!TIME
='SCROLL_TESTER.XLSX'!SERIES1
='SCROLL_TESTER.XLSX'!SERIES2
='SCROLL_TESTER.XLSX'!SERIES3
='SCROLL_TESTER.XLSX'!SERIES4
Then Excel states an error saying that either the Formula or Worksheet
or Cell Reference is incorrect. I believe the only error is in the
actual naming and defining of the range through the OFFSET function
however when I double check it seems correct to my knowledge (due to
my lack of).
If anyone could provide some help into getting the Scroll/Zoom
function to work properly for the Candle Chart. I have seen it done
before. Or if you know any other method/program that has greater
efficiency then please also suggest. Thanks for your help in advance.
I can also send you the original workbook if it is of any help.
Cheers,
Chris