Filter Function Limitation with Dynamic Ranges?

Skiier89

New Member
Joined
Jun 14, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,835
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
INDIRECT only works with actual cell addresses, not dynamic ranges. Could you not use tables instead of dynamic ranges?
 

Skiier89

New Member
Joined
Jun 14, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Thank you!

I have to keep this in mind about indirect - no dynamic ranges will work with indirect.

Using a table may be a good solution if I can format it correctly. The initial issue was that each sheet (2014, 2015, etc.) has an additional filter on it that cannot be housed in a table. This is what sent me down the path of named ranges instead of tables.

However, only columns A through C on each year's sheet utilize a filter. If I created a table out of the remaining columns (column D onward), I can see how this could ultimately work. It really makes me appreciate the fine differences between tables and dynamic ranges.

But now I have to ask - if there is a table that exists on sheet 2017 for example, and sheet 2017 then is copied to create sheet 2018, is there a way to force Excel to rename the table accordingly (just like a local range)? Unfortunately I have to assume that any other users are not going to update names for the ranges or tables.
 

Forum statistics

Threads
1,182,107
Messages
5,933,697
Members
436,905
Latest member
Ibraeh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top