Indirect functions to decide the range of data in Graphs Excel 2007

all4excel · 01-28-2008, 02:19 PM

Can we use the Indirect functions to decide the range of data in Graphs Excel 2007

I would be having dynamic data, dynamic as in the Rows would change always if anything is entered in the Main Data sheet.

Now based on this data in the Result sheet the graph should get formed..

If I have 4 rows of data then there would only appear 4 slices in a Pie Chart, however if the data increases to 5 rows then it should dynamically take 5 rows of data and come up with 5 slices or more..

[ = INDIRECT("'RESULT'!$B$2:$C$"& COUNT(C:C)+1) ]

I want to use the above formula in which the COUNT(C:C) would decide the number of rows present as it contains mathematical data and can be counted easily.

The range of the data should be dynamic for the Pie chart...

Thanks in anticipation...

Can we use the Indirect functions to decide the range of data in Graphs Excel 2007

I would be having dynamic data, dynamic as in the Rows would change always if anything is entered in the Main Data sheet.

Now based on this data in the Result sheet the graph should get formed..

If I have 4 rows of data then there would only appear 4 slices in a Pie Chart, however if the data increases to 5 rows then it should dynamically take 5 rows of data and come up with 5 slices or more..

[= INDIRECT("'RESULT'!$B$2:$C$"& COUNT(C:C)+1) ]

I want to use the above formula in which the COUNT(C:C) would decide the number of rows present as it contains mathematical data and can be counted easily.

The range of the data should be dynamic for the Pie chart...

I want this to be solved using MS Excel and not VBA preferably..

Thanks in anticipation...

Please anyone someone ........help me or offer a solution...

**vane0326** · 02-02-2008, 01:12 AM

Hi All4excel,

I tried to use non-volatile formulas because at times your workbook might be sluggish.

I used this formula below to define your name range formula.

=A!$C$2:INDEX(A!$C$2:$C$65536,MATCH(2,1/((A!$C$2:$C$65536<>""))))

Hope it helps!

all4excel · 02-02-2008, 11:57 AM

I am at home where i dont have Office 2007, so can u please resend the file in 97-2003..

I will be on a leave for a few days so wont be able to check this solution at work untill then..

I am sorry for this request, however i can t wait to see the solution...

Thanks for the solution...