Dynamic Offset reference

[Manojkumar S]

I had created dynamic chart based on Offset, chart will display the data from table. Currently reference for offset is static and i would like to keep it dynamic so that when the starting reference is changed i don't need to keep on changing every time.

Table Name is "Trend_Data"
Sheet Name where table is created is "Data"

Below code will find the starting location of table located. Trend_Start_Col is the Name Manager created for the below formula and used in offset formula.

=SUBSTITUTE(ADDRESS(1,COLUMN(Trend_Data[#Headers])+1,1),"$1","")&"$"&ROW(Trend_Data[#Headers])

Offset formula used for creating dynamic chart.

=OFFSET("Data!" & (Trend_Start_Col), 0, 24, 1, 40)

Note: Numbers mentioned in offset formula are achieved dynamically. For easy readability i hadn't provided the complete formula.

Now i need to link the Sheet name "Data" with the Name manager while updating in the chart. Above formula throws error and i could not able to achieve.

Is there any way of creating dynamic offset reference?

 

[GraH - Guido]

would you be needing something like this
http://www.criticaltosuccess.com/dynamic-charts-in-excel-resize-with-offset-function-named-ranges/
https://chandoo.org/wp/dynamic-chart-data-series/

 

[Manojkumar S]

Yes but i had no issues in creating the dynamic chart. First parameter [reference] to Offset is hard-coded and i need to make it dynamic based on the formula.

 

[NARAYANK991]

Hi ,

See if using the INDIRECT function helps , as in :

=OFFSET(INDIRECT("Data!" & Trend_Start_Col), 0, 24, 1, 40)

For this to work , the named range Trend_Start_Col has to evaluate to a string.

If this does not work , please upload your workbook.

Narayan

 

[Manojkumar S]

No it doesn't work. I had tried already, when INDIRECT was used sheet was not referred only value from Trend_Start_Col is calculated.

Attached my report file and you can refer B14 column for the formula.

 

[Manojkumar S]

Hello,

Anybody knows how to achieve this ?

Thanks for your support in advance

 

[Haz]

=OFFSET(INDEX(Trend_Data[#Headers],,2), 0, 24, 1, 40)