Chart dynamic data range

[serhito]

I have a set of data for stocks in the following format :
open,high,low,close

I retrieve the data with a index and match function, so the number of lines varies depending on which data I pull.
I then use this data to create a chart.
The problem lies with the fact that the number of data varies and therefore alter the chart data range.
How can the data range be modified automatically ?
I tried to use a table but it won't resize automatically.

Thanks.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[serhito]

I can't post the real spreadsheet due to some confidential information.
So here is what I am trying to explain. In the GREEN data area, this is where I am ALWAYS getting the information by using an INDEX MATCH formula referring to another spreadsheet. I have the ticker symbol in cell D3 which guides which data is retrieved. So the GREEN data range will vary in length. I am showing the YELLOW area as an example.
Now you can see that the YELLOW area will be shorter, but the Chart will still use the old area (longer). Result, the chart in YELLOW.example.PNG

[Glenn Kennedy]

I didn't ask for the real sheet. Mock up a sample. I am not going to mock one up for you!!

[serhito]

i got one attached

[Glenn Kennedy]

There were no formulae in the file!! Oh well. I used formulae that are needed if selecting a Named Range which contains formula blanks:

=$U$5:INDEX($U$5:$U$58,SUMPRODUCT(--(LEN($U$5:$U$58)>0)))

I called it "Date". CTRL-F3 to view/Edit. I then adjusted the range of the chart to include column U. I then adjusted the X-axis range to be =Sheet1!Date and finally deleted the dataset "Date" from the Series box of the Select Data Source box.

Is this what you wanted?

[serhito]

Thanks Glenn ! I will check it out.