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.