Dynamic Chart Query

[leopaulc]

Dear Sirs,

I was trying to make dynamic chart in my level.... But it was not working....

The real issues which I am facing is,
1. There is many pages in the workbook, and i have to select the data from that particular sheet.

I used the below formula to find the relevant data with reference to a cell. I gave the freedom to select the sheet name from the reference cell.
the formula is,
=OFFSET(INDIRECT("'"&$B15&"'!$A5:$A600"),0,0,COUNTA(INDIRECT("'"&$B15&"'!$A5:$A600")))
The reference cell is B15.

I entered this formula to Name Manager (Ctrl+F3) with a Name.
In graph, I tried to enter the formula in axis value wtih the Name (which is provided in the name), excel shows, " That function is not Valid ".
After clicking OK to proceed, excel is showing that the range is already selected.

Please help...
Where is the error occured???

Thanks for the support

[Admin]

Hi Leo,

I think INDIRECT doesn't work with dynamic name ranges.

Not sure about this, but you could try this (http://www.contextures.com/xlDataVal02.html#Dynamic) method.

[leopaulc]

Thank you for the reply..
I tried to do the method you mentioned.. But it is not worked with 2007..

Thanks again for the support....

[SDruley]

leopaulc,

I believe the practical solution here is to create range names of the data subsets that are strategic to your business, then auto create charts using a drop down list of these same range names. The vba code would be fairly simple. So you would select 3rdQ11Profits on the drop down list and the macros would be tied to the dropdown and instantly create the chart, say, to the right of the drop-down. Then select 1stQInv12 and the old chart disappears and the new one is constructed showing the inventory levels. Once you moved the chart from its position it would be immune to deletion and you could build a sheet of charts important for a particular meeting.
The non vba solution is very cerebral and involves creating a specialized set of range names.

[Rasm]

If you are interested -- I will write an example in VBA --- But post a xl2007 sheet with a series of data you want to chart --- also specify the chart type you want to use. The code will just be an example --- so you will need to take the time to understand the code and fit it to your needs. I make SPC charts --- so essentially making a skeleton (with my limits, scales, hearders and so on) --- then add the actual data serires later --- this allow me to update my chart every 6 seconds without having tp recreate the chart ---- I simply update the data in the plotarea.

[SDruley]

Rasm,

The fact that your are creating charts to measure your degree of process stability is noteworthy. Apparently, you don't need any help from me, so let's just say that I am learning from you. Good work.

Steve

[Rasm]

Steve
I need all the help I can get -- just trying to share - thats all
Rasm