Named Range, Pivot Table failure

[Will_DACB]

Hello, 

I've come across a problem that I've not seen before and I can't find a fix anywhere. 

My source data is 31 columns wide and is bound under a Dynamic Named Range using the OFFSET, COUNTA formula. When I test the range on the source it's fine and goes from A1 to AE3483 without any problems; however, when I apply the range to a pivot table, it stops at column AB. 

As a consequence, data in the final columns does not appear in the list for my pivot table and I cannot use it.

(For this task I must use a Dynamic Range as the source data is constantly being updated and I want to be able to easily refresh my pivot tables without having to manually adjust the ranges - because there are many)

Can anyone offer some advice into why the pivot table isn't counting across all columns? 

Here is an example of the formula being used: 

=OFFSET('Dash Source'!$A$1:$A$500000,0,0,COUNTA('Dash Source'!$A$1:$A$500000),COUNTA('Dash Source'!1:1))

[#2]

Try using this formula for your dynamic range:
=‘Dash Source’!$A$1:INDEX(‘Dash Source’!$A:$XFD,
COUNTA(‘Dash Source’!$A:$A),
COUNTA(‘Dash Source’!$1:$1))

[#3]

Aaaah, thank you! You know, all it needed was the $1:$1 at the end and now it works! Very silly of me to forget that bit... Cheers!

[#4]

You’re welcome. Just the same, INDEX is preferable over the volatile OFFSET for dynamic ranges.