Forum Discussion

Copper Contributor

Jun 06, 2019

Solved

Dynamic range of data for graph

After many days of trials I must ask for help.

I need to create a graph from range of data, which first cell is defined but the last cell changes based on value in another cell. All data is in one column so only row number changes. A have attached an example.

I hope this is possible.

Graph.xlsx15 KB

excel

Formulas and Functions

Twifoo
Jun 07, 2019
Waceks
In the attached file, I entered "=DynamicSeriesValues!SeriesValues" in the Series Values box of the Edit Series tab. SeriesValues is a named range with this formula:
=Arkusz1!$C$3:INDEX(Arkusz1!$C:$C,Arkusz1!$A$3+2)
Please modify the value in A3 to see the effect of the dynamic range on the chart.
DynamicSeriesValues.xlsx14 KB

5 Replies

Twifoo
Silver Contributor
Jun 06, 2019
To return the ADDRESS of the last cell, use this formula:
=ADDRESS(A3+2,3)
- Waceks
  Copper Contributor
  Jun 07, 2019
  TwifooThank you for your answer, but this is not the problem I have. Such formula I wrote already in cell F4.
  The problem is how to use it in graph. The range like "=Sheet1!$C$3:$C$32" is OK but "=Sheet1!$C$3:ADDRESS(A3+2;3)" doesn't work.
  - Twifoo
    Silver Contributor
    Jun 07, 2019
    Waceks
    In the attached file, I entered "=DynamicSeriesValues!SeriesValues" in the Series Values box of the Edit Series tab. SeriesValues is a named range with this formula:
    =Arkusz1!$C$3:INDEX(Arkusz1!$C:$C,Arkusz1!$A$3+2)
    Please modify the value in A3 to see the effect of the dynamic range on the chart.
    DynamicSeriesValues.xlsx14 KB