Forum Discussion
can a filtermatrix be definded as table?
It would be better to exploit the new dynamic array notation than reverting to old-style dynamic range definitions. In the attached, I have filtered the value and number columns of the table individually so that there is a separate spilled range for each filtered set. The alternative is to filter as you have done and pick the columns of the filtered range out by using INDEX. I tend to prefer sheet-local names for charts; it seems to make the best of a user-hostile interface.
It would be better to exploit the new dynamic array notation than reverting to old-style dynamic range definitions. In the attached, I have filtered the value and number columns of the table individually so that there is a separate spilled range for each filtered set. The alternative is to filter as you have done and pick the columns of the filtered range out by using INDEX. I tend to prefer sheet-local names for charts; it seems to make the best of a user-hostile interface.
- Thanks PeterBartholomew1,
its a good idea to filter each rows and i did not now the # notation.
Now my diagrams can be as flexible as necessary for me.
Why does the $A$1# notation has to be used in the name manager, but cannot be used in the diagrams as x-data?,- The # notation is absolutely central to modern dynamic arrays so, having used it here, you should be well-placed to use it throughout your worksheet. It is also possible to use it with a named ref, so
anchorCell#
could be a dynamic array and
@anchorCell#
would be the single cell in line with the cell containing the formula.
As for why it works in Name Manager, it is simply a question of how old the section of code is. I think charting is much as it was in 2007 whereas the # notation was new late 2018.
