Ask Your Question

Multiple AutoFilters

asked 2018-12-25 12:35:06 +0200

alex72gr gravatar image

updated 2020-07-20 09:58:45 +0200

Alex Kemp gravatar image

Hello! 1) In my spreadsheet I have created an autofilter including multiple columns. Each column has it's own "filter" (header cell). The problem is that when I try to select another "region" of the spreadsheet and create another autofilter, the first autofilter disappears! Is there ANY way to keep both autofilters? 2) The second autofilter I need to create would be by far more flexible if I could create JUST ONE header cell for the selected "region" of my spreadsheet (this "region" is actually a "range" of cells which includes many columns). So, what I need is a single header cell where from I could select ANY of the values of the above mentioned "region" of my spreadsheet. Is this possible? Thanks a lot!

edit retag flag offensive close merge delete


You need to define a data range for the area to which you want to apply a filter.

Regina gravatar imageRegina ( 2018-12-25 13:12:52 +0200 )edit

I do so when I apply a filter. Otherwise the filter will not work at all. The problem is 1) adding a new filter (for example a filter on another column) and 2) filter data from an area from one single header cell. Thank you anyway! :)

alex72gr gravatar imagealex72gr ( 2018-12-25 21:45:19 +0200 )edit

Menu/Data/Define range

m.a.riosv gravatar imagem.a.riosv ( 2018-12-25 23:58:44 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-04-29 10:17:22 +0200

m.a.riosv gravatar image

Define filters from cells is what Menu/More Filters/Advanced filter , does.

edit flag offensive delete link more


yeah, you are right, but imho it's not as easy as the OP wanted to have it ... he asked for a single! header cell to address ANY value in the range ...

newbie-02 gravatar imagenewbie-02 ( 2020-04-29 12:17:46 +0200 )edit

answered 2020-04-29 08:04:02 +0200

newbie-02 gravatar image

updated 2020-09-07 00:41:13 +0200

puuuhh, old question, not yet answered?

the comments from @Regina and @m.a.riosv clarify the problem, to have more than one auto-filtered range on a sheet you need to define 'data ranges', sometimes also called database ranges.

'data ranges' and 'named ranges' are different in function (dunno how and why but that), to apply autofilters you need 'data - define range'.

(for reasons of comfort?) calc also allows you to create one - and only one! - autofilter per sheet without defining a range first, if you call autofilter somewhere where no range is defined then calc will automatically try to use the smallest rectangular area delimited by empty rows and columns as temporary database area. if it doesn't find a suitable area (e.g. nothing that could be used as headers) then 'autofilter not possible' will appear.

this area and the filters defined in it are cleared as soon as a autofilter is applied to another area that is not explicitly defined.

a similar automatism works for standard filters 'data - more filters - standardfilter', this can be done for more than one area of a sheet.

where from I could select ANY of the values of the above mentioned "region"

imho it's very difficult - and would be a logical problem - to select filtering conditions for multiple columns from one cell and / or to use values for filtering from other columns, but you may play with everything up to programming macros ...

my state of knowledge as of now, no warranties

the filtering situation and options are powerful, and thus not 'simple' and require some effort to learn about ... the behaviour is idiosyncratic in some respects, e.g. automatically expanding defined areas, try and find out, and be aware that 'comfort' in sight of some users may cause irritations for others.



gave 'one header for any value of a range' one more thought ...
with [data - validity] you can predifine a selection list for a cell presenting all values appearing in a range, even sorted,
with advanced filter you can define multiple definition ranges for multiple filtering ranges, and in the definition you may reference the cell with the selected value, 'or'-ing it by having it in multiple columns in separate rows,
and now the pity :-( ... advanced filter is not dynamic ... after every change in the definition you have to re-apply it to the filtered ranges to get it evaluated :-( but may be this combination comes near(est) to OP's request?
and now the challenge ... a macro to refresh / re-apply the filtering for all ranges once the selection cell changes it's value, ... never say never, but complex and complicated ... 'dynamic mode' for filtering - as it is for conditional formatting - would be easier,
ok, as i have already written such long ... perhaps for the use case of the OP the following construct would be good? one 'selection cell' out of the data area with data - validity allowing all occuring data values, and for all cells in the data ... (more)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-25 12:35:06 +0200

Seen: 2,098 times

Last updated: Sep 07 '20