0

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 the Offset worksheet function, Excel is not using the value (currently being 9), but the cell reference, where that name is defined (A3).

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
  • 1
    You say "whole table", but is this in an Excel table format? A defined table would probably help. – Rey Juna Sep 27 '18 at 15:50
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.