Forum Discussion

Copper Contributor

Aug 12, 2019

Named Range, Pivot Table failure

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...

Silver Contributor

Aug 12, 2019

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))

Will_DACB

Copper Contributor

Aug 13, 2019

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!

Twifoo
Silver Contributor
Aug 13, 2019
You’re welcome. Just the same, INDEX is preferable over the volatile OFFSET for dynamic ranges.