Dynamic Chart Range

[Dokat]

Hi,

I am trying create a dynamic chart range where chart change as the values get updated. However when a define a name range using below formula and try to create a chart based off it i keep getting error messages. Can anyone help me solve the problem. Thanks

INDIRECT("A"&OFFSET(INDIRECT(CELL("address",INDEX(rng,ROWS(rng)))),-66,)&":"&"A"&OFFSET(INDIRECT(CELL("address",INDEX(rng,ROWS(rng)))),-52,)))

 

[Hui]

Can you post the file?

 

[Dokat]

Please see attached. "ChartData" is the range i want to dynamically update. Basically i want chart to be based off data from cell Sheet1!(A38:A52)

Thanks for your help

 

[NARAYANK991]

Hi ,

What is the problem here ?

Narayan

 

[Dokat]

I am trying to update a chart with Dynamic range

I created a name range called ChartData based of below formula. I want formula to read as Sheet1!(A38:A52) and graph to update based on the outcome of this formula

("A"&OFFSET(rng,ROWS(rng)-66-1,,1)&":"&"A"&OFFSET(rng,ROWS(rng)-52-1,,1)))

I type name range (ChartData) in Chart Data Range field and it returns nothing. I cant figure out what i am missing.

 

[NARAYANK991]

Hi ,

1. The formula for the named range has to return the data in the form of an array , as shown below :

{38;39;40;41;42;43;44;45;46;47;48;49;50;51;52}

This is the set of values in the range $A$38:$A$52 ; these endpoints are obtained by subtracting 66 from 104 and 52 from 104.

Hence , it is clear that the formula has to have the INDIRECT function as the outer-most function ; you need to pass the string $A$38:$A$52 to this function to get the desired values in the form of an array.

To get the string , use the same construct that you have used earlier :

"Sheet1!" & ("A"&OFFSET(INDIRECT(CELL("address",INDEX(rng,ROWS(rng)))),-66,) & ":" & "A" & OFFSET(INDIRECT(CELL("address",INDEX(rng,ROWS(rng)))),-52,))

Define ChartData as :

=INDIRECT("Sheet1!" & ("A"&OFFSET(INDIRECT(CELL("address",INDEX(rng,ROWS(rng)))),-66,) & ":" & "A" & OFFSET(INDIRECT(CELL("address",INDEX(rng,ROWS(rng)))),-52,)))

Click on Select Data , Add , and in the data entry box , type in :

=Sheet1!ChartData

Click on OK.

Excel will convert your data entry to :

='Test File (7).xlsx'!ChartData

and the chart will display correctly.

Narayan

 

[Dokat]

Thank you so much Narayan. It worked! Indirect was missing piece of the puzzle. I appreciate you taking time to help

 

[Hui]

If the two numbers 66 and 52 have any meaning then you can simplify this to:

=OFFSET(Sheet1!$A$1,MAX(rng)-66-1,,66-52+1,1)

or use cell references instead

=OFFSET(Sheet1!$A$1,MAX(rng)-B1-1,,B1-B2+1,1)
where B1 has 66 and B2 holds 52