Forum Discussion
can a filtermatrix be definded as table?
i have data, which i want to filter for e.g. time. so i add two cells with start and end time.
now i add the filter function on the data table to get the wanted time span.
how can i easily draw a diagram of this filtered data without using the name manager and the offset function?
for tables the diagrams are dynamic
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.
3 Replies
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.
