0

I have a sheet with historical data. I have multiple graphs which references this data its mainly data(y-axis) and date(x-axis) This is how I refer date ='Bug Data'!$B$10:$B$21

Similarly for the other data I refer it by changing the column name

Instead of me updating the graphs each time I want to change the range. Is there a way to parameterize the row in my case 10,21 to a particular cell. Such that when I change the range to 22 35 all the graphs formula update

something like this where A1,A2 of sheet 1 contains the range

='Bug Data'!$B$(Sheet1)!$A$1:$B$(Sheet1!$A$2)

user3018934
  • 121
  • 2
  • 6

2 Answers2

0

You can try using the OFFSET function in a named range and then use that named range in your chart definition, as in the following question: Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)

Community
  • 1
  • 1
nutsch
  • 5,922
  • 2
  • 20
  • 35
0

Indirect works fine. Just set all the graphs to indirect(A1), Indirect(A2) etc.

and then in A1, A2, etc. Put the value of the range. ie. The value of the Range A1 is the string "B10", the value of cell A2 is the string "B21")

sten
  • 380
  • 1
  • 5
  • 14