0

I would like to create a dynamic chart. For example, I would like to plot the Apple and Banana series dynamically using the table below (Table).

    A   B   C       D           E
1   X   Y   Z       Min/Max     Row
2   1   1   Apple   Apple  Min  2
3   2   2   Apple   Apple Max   3
4   3   3   Banana  Banana Min  4
5   4   4   Banana  Banana Max  6
6   5   5   Banana

Thus far, I have created 2 formulas using the OFFSET function as shown below:

=OFFSET(Sheet1!$A$2,0,0,Sheet1!$E$3-Sheet1!$E$2+1)
=OFFSET(Sheet1!$B$2,0,0,Sheet1!$E$3-Sheet1!$E$2+1)

This will plot the Apple series, but next I would like to plot the Banana series and I'm not sure how to modify the OFFSET Reference to start at the correct row.

What I want are formulas like this but with proper OFFSET Reference syntax:

=OFFSET(Sheet1!$A$**Sheet1!$E$4**,0,0,Sheet1!$E$5-Sheet1!$E$4+1)
=OFFSET(Sheet1!$B$**Sheet1!$E$4**,0,0,Sheet1!$E$5-Sheet1!$E$4+1)

Also, if you know of a more elegant way to define the OFFSET Height, please share.

Thanks in advance for your help!

Cheers

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
Devin
  • 21
  • 1
  • 4
  • I don't see what part makes it dynamic - do you have a datavalidation drop down somewhere to choose apple or banana? Anyway, some possible solutions for you may be here : https://stackoverflow.com/questions/18272728/dynamic-chart-range-using-indirect-that-function-is-not-valid-despite-range-hi – Solar Mike Jun 05 '17 at 06:41
  • Column E is calculated using a MAXIFS function with Column C (Apples/Bananas)... adding a new line would result in a new range of that data. Inserting the OFFSET function into a formula to plot a series in a Scatter chart would make the chart dynamic. – Devin Jun 05 '17 at 16:11

0 Answers0