Dynamic chart in Excel?

Question

infographics

There is a line with values, the range is from a1 to a8, these values ​​set the graph. In cells a6, a7, a8, depending on the situation, the values ​​can take on #VALUE! or just numbers
You need to implement the following:
1. If the cells contain all the values, then the graph is set in the range a1:a8
2. If the cells a6, a7, a8 have the value #VALUE! (the algorithm is such that if in a6 #VALUE!, then in subsequent ones too), then the graph is set by the range a1:a5
Actually, tell me the formulas

Answer 1

Guided by this
I have Excel 2007 English.
The final document is here .
Step by step:
1) Write a formula to determine the range of cells. I was too lazy to think it over, I just used “the number of non-empty among A1:A8, but it will not work, for example, for A1:A6 non-empty, A7 = #VALUE!, A8 is non-empty. If there are difficulties with this formula, I will do it, it is not difficult.
My formula turned out to be =OFFSET(Sheet1!$A$1;0;0;COUNT(Sheet1!$A$1:$A$8);1)
comments: OFFSET changes the given range (in this case one cell $A$1), shifting its upper left corner by 0 rows/columns (i.e. not shifting) and changing its height to some number and width to 1 column. The new height is determined by this part of the COUNT(Sheet1!$A$1:$A$8) formula, i.e. the number of non-empty cells in the range $A$1:$A$8. According to the condition, you need a longer one, with IFs.
Comments:
- hereinafter $A$1 is the same as A1, only the coordinates are absolute, i.e. if you insert a row column, they will remain the same and will refer to other data. You can use relative ones instead - it's just A1, they will change when you add/remove rows/columns.
- on the Internet they write examples of formulas for OFFSET with commas separating arguments, it didn’t work out for me, but it turned out with semicolons. Probably depends on the locale.
- the performance of the formula can be checked by inserting it into any cell and surrounding it, for example SUM, i.e. =SUM(OFFSET($A$1;0;0;COUNT($A$1:$A$8);1))
2) We need to name this dynamic range somehow. This is done by the Formulas - Define Name command. There Name - whatever, for example MyName, and the value - the formula from paragraph 1.
3) You need to set the graph = 'Sheet1'! MyName as a horizontal range. I did this: Graph - Select Data - Legend Entries (Series) - Add - Series Values ​​\u003d Book1! MyName (any name).