Hi,
I've found a lot of help here and want to thank the true experts..
To keep this simple, my question is whether it is possible to use a dynamic range in a filter function.
Background - I have local defined ranges on multiple sheets in a workbook. Each sheet is a year (i.e. 2014, 2015, 2016, etc). The defined range 2016!Name is updated to 2017!Name when the sheet is copied to 2017. Additionally, these ranges are created using either an offset or an index function so that they are dynamic - they expand or contract to the range of rows or columns that are populated each year the sheet is copied.
On a summary sheet, I have a filter function and use indirect() to create / establish the filter array as well as to satisfy the include argument.
When the defined local range on each year's worksheet refers directly cell references, the indirect function correctly establishes the filter array on the summary sheet (and works for the include argument as well). However, when the defined local range is dynamically defined on each year's worksheet, the filter returns #REF error.
When I remove the indirect function and directly type in the defined range *even when the range is dynamically created*, the filter on the summary sheet works.
I would really like to figure out if this is a limitation of the filter function or something else. It doesn't appear to be anything with the inputs that refer to or call upon the defined ranges. The filter works with the dynamic range when manually input, but cannot handle it when the range is called upon using indirect().
Disclaimer - I am aware that indirect is volatile, and because of this great community, I feel like I have a good understanding of creating filters. I think this issue is a little different though. I would like to keep the ranges dynamic, and keep indirect() in the filter to call upon them. But seemingly, I can't do both.
Any suggestions are greatly appreciated. Thank you in advance!
I've found a lot of help here and want to thank the true experts..
To keep this simple, my question is whether it is possible to use a dynamic range in a filter function.
Background - I have local defined ranges on multiple sheets in a workbook. Each sheet is a year (i.e. 2014, 2015, 2016, etc). The defined range 2016!Name is updated to 2017!Name when the sheet is copied to 2017. Additionally, these ranges are created using either an offset or an index function so that they are dynamic - they expand or contract to the range of rows or columns that are populated each year the sheet is copied.
On a summary sheet, I have a filter function and use indirect() to create / establish the filter array as well as to satisfy the include argument.
When the defined local range on each year's worksheet refers directly cell references, the indirect function correctly establishes the filter array on the summary sheet (and works for the include argument as well). However, when the defined local range is dynamically defined on each year's worksheet, the filter returns #REF error.
When I remove the indirect function and directly type in the defined range *even when the range is dynamically created*, the filter on the summary sheet works.
I would really like to figure out if this is a limitation of the filter function or something else. It doesn't appear to be anything with the inputs that refer to or call upon the defined ranges. The filter works with the dynamic range when manually input, but cannot handle it when the range is called upon using indirect().
Disclaimer - I am aware that indirect is volatile, and because of this great community, I feel like I have a good understanding of creating filters. I think this issue is a little different though. I would like to keep the ranges dynamic, and keep indirect() in the filter to call upon them. But seemingly, I can't do both.
Any suggestions are greatly appreciated. Thank you in advance!
