I have an Excel sheet (called Sheet1
), where I regularly add some data. By pushing a button, a summary of that data gets written on a next page (called Evolution
), and on that next page, there's also a graph, and that graph seems to be wrong in the sense that it does not update: the graph has been set as having its "Chart data range" like =Evolution!$B$2:$F$10
, but when a row gets added to this range, it does not automatically update to =Evolution!$B$2:$F$11
.
There's one thing which might be helpful: I already have defined a name Evolution_Total_Count
(in cell A3), which calculates the amount of rows I need for the graph, but here's where the fun starts: I'm thinking of a chart data range like this:
=Offset(Evolution!B2:B2;Evolution_Total_Count;5)
- The idea is:
- Start with cell B2
- The number of rows to take can be found in the name
Evolution_Total_Count
- The number of columns equals 5 (there are five columns indeed)
- The reality is:
- This is not the correct way to use the
Offset
worksheet function. (But what is the right way and how can I test this, how can I test a formula, returning a range?) - When I enter the name
Evolution_Total_Count
in theOffset
worksheet function, Excel is not using the value (currently being 9), but the cell reference, where that name is defined (A3).
- This is not the correct way to use the
Does anybody know how I can define the chart data range of my chart, preferebly using the name Evolution_Total_Count
? (I prefer not to create a name for the whole table.)
For clarity purposes, hereby a sample of what my Evolution
worksheet looks like:
| A B C D E F
---+--------------------------------------------------------------------------------
1 |
2 | Amount of Date Still open Still open First answer Second answer
| entries (amount) average average average
3 | 9 20/07/2018 7 12,14285714 8,368421053 8,863636364
4 | 23/07/2018 8 13,625 7,95 8,869565217
5 | 26/07/2018 11 17,63636364 7,043478261 7,785714286
6 | 06/08/2018 10 22,5 6,272727273 7,444444444
7 | 09/08/2018 10 24,8 5,621621622 6,95
8 | 10/08/2018 10 18,9 5,413043478 6,25
9 | 11/09/2018 13 30,38461538 4,677419355 7
10 | 17/09/2018 11 37,54545455 5,117647059 7,109589041
11 | 26/09/2018 10 27,5 4,901234568 6,823529412
Best Answer
You can set up your data as a table and base your graph off of that. Your
Chart Data Range
would still be a data range instead of the table name and it would include the header, so that if row 1 was your header row your range would be:=Evolution!$B$1:$F$10
When you add a row, the range will adjust to
=Evolution!$B$1:$F$11