I have created a dynamic range from a pivot table using the FILTER function.
I have then created named ranges using the offset function i.e. =OFFSET(Report!$K$9,,,COUNTA(Report!$J$9:$J$100)). This extends beyond the current data so there is room to grown.
When I check the "refers to" section, the reference range includes the complete reference rather than just the range with data in it.
Because of this, the data I use to create a chart does not have a dynamic axis.
Please see attached file. Any help greatly appreciated.
Hi Eric,
Welcome to our forum! Thanks for sharing your file. This makes it much easier to diagnose.
Your OFFSET formula is counting cells J9:J100 to see if they contain data. Cells J35:J109 contain an IF formula that's returning blank denoted by two double quotes which are invisible on the face of the cell, but not in the formula bar.
Blanks returned by double quotes are included in the count. So, all you need to do is delete those IF formulas.
That said, I see that you are using the dynamic array FILTER function. All you need to do to create a dynamic range is define a name for the spilled array e.g. in the 'Refers to' field on the chart_axis name enter this formula: =Report!$J$9#
Because it uses the pound/hash spilled array operator it will automatically pick up the range returned by the FILTER formula. No need for OFFSET at all.
Repeat for the other defined names that reference FILTER. More on the spilled range operator here.
Hope that helps.
Mynda
