Using Indirect Function for Charts in Excel 2007

[linabhatla]

Hi Chandoo... Kindly help me with this


I have got a range written in a cell and I want to use the range for the chart.


Eg: Cell A12 contains $B$1:$B10.

I was trying to use Indirect function for the purpose but it doesnt't seem to work.


Is it possible?

 

[vasim]

Hello Linabhatla....


I don’t know if indirect can help you, but this is something I belive should give you the exact result..

Your data seems to be in column B whose rows are not fixed, you can use offset formula.

Go to formula - name manager & prepare a new name range.

chart_data=OFFSET(Sheet1!$B$1,0,0,SUMPRODUCT(--(Sheet1!$B$1:$B$5000<>"")),1)

Go to your series data delete the range visible (not the sheetname) & press F3, name box will appear, select your name range & OK.

Now you can add data in column B were automatically the chart would be changed.

 

[linabhatla]

Thanks for the response. To give you the complete problem - I have four cells with four different ranges. These ranges are dynamic and change with the source data changing. I want to make a single graph with these four ranges.


I have been struggling with this for long. Kindly help.

 

[vasim]

You can define 4 names as Chart_data_1,Chart_data_2 and so on.... can you expand your questions a little more...

 

[Luke M]

linabhatla,

Let's assume your 4 cells are A12:A15. It sounds like these cells are building ranges using formulas. Now, under Insert - Name - Define, define 4 names (Chart1, Chart2, etc)

using a formula like:

=INDIRECT($A$12)


Then, in your chart, you can define the x and/or y data as:

='My books.xls'!Chart1

Repeat as needed to add the other 3 names

 

[linabhatla]

Hey Luke... thanks man.. but it doesn't seem to work

 

[Luke M]

What part is not working correct? Were you able to setup the named ranges?

what are you defining in the chart, and what is the formula?

 

[linabhatla]

1st issue is the indirect formula in the name manager is not allowing sheet name to be static.


2nd issue is I am unable to use the Chart names in Chart Source. When I enter the name it selects the required area, however, as soon as I press "Enter", it gives me error message to check the formula.

 

[Mike86]

Does your named range function look like this?:


=INDIRECT("'"&"Sheet2"&"'!"&Sheet2!$A$1&":"&Sheet2!$B$1)


This locks in the sheet and allows the starting and ending cells to be in a1:b1.


Three more like that with different cell references will give you the four ranges.


When you make your chart, go into the "Select Data Source" box. Don't put the ranges in the "Chart Source" line. Under "Legend Entries" click "Add". Type in whatever you want as a series Name. For the series values, type in "=", click on the sheet's tab name, then type in your first named range. Repeat for the other series.

 

[linabhatla]

Hey Mike... thanks for the response I used the indirect function exactly the way you elaborated minus the colon part since my range is written in a cell.


I then tried using the chart name in the legend entries but it gives me the same error message as it gives if I use the chart name in "data source". . Is there a way you can send me the demo.