Home

Named Range, Pivot Table failure

%3CLINGO-SUB%20id%3D%22lingo-sub-800214%22%20slang%3D%22en-US%22%3ENamed%20Range%2C%20Pivot%20Table%20failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-800214%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20come%20across%20a%20problem%20that%20I've%20not%20seen%20before%20and%20I%20can't%20find%20a%20fix%20anywhere.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20source%20data%20is%2031%20columns%20wide%20and%20is%20bound%20under%20a%20Dynamic%20Named%20Range%20using%20the%20OFFSET%2C%20COUNTA%20formula.%20When%20I%20test%20the%20range%20on%20the%20source%20it's%20fine%20and%20goes%20from%20A1%20to%20AE3483%20without%20any%20problems%3B%20however%2C%20when%20I%20apply%20the%20range%20to%20a%20pivot%20table%2C%20it%20stops%20at%20column%20AB.%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20consequence%2C%20data%20in%20the%20final%20columns%20does%20not%20appear%20in%20the%20list%20for%20my%20pivot%20table%20and%20I%20cannot%20use%20it.%3C%2FP%3E%3CP%3E(For%20this%20task%20I%20must%20use%20a%20Dynamic%20Range%20as%20the%20source%20data%20is%20constantly%20being%20updated%20and%20I%20want%20to%20be%20able%20to%20easily%20refresh%20my%20pivot%20tables%20without%20having%20to%20manually%20adjust%20the%20ranges%20-%20because%20there%20are%20many)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20offer%20some%20advice%20into%20why%20the%20pivot%20table%20isn't%20counting%20across%20all%20columns%3F%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20of%20the%20formula%20being%20used%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOFFSET('Dash%20Source'!%24A%241%3A%24A%24500000%2C0%2C0%2CCOUNTA('Dash%20Source'!%24A%241%3A%24A%24500000)%2CCOUNTA('Dash%20Source'!1%3A1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-800214%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENAMED%20RANGE%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epivot%20table%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-800450%22%20slang%3D%22en-US%22%3ERe%3A%20Named%20Range%2C%20Pivot%20Table%20failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-800450%22%20slang%3D%22en-US%22%3ETry%20using%20this%20formula%20for%20your%20dynamic%20range%3A%3CBR%20%2F%3E%3D%E2%80%98Dash%20Source%E2%80%99!%24A%241%3AINDEX(%E2%80%98Dash%20Source%E2%80%99!%24A%3A%24XFD%2C%3CBR%20%2F%3ECOUNTA(%E2%80%98Dash%20Source%E2%80%99!%24A%3A%24A)%2C%3CBR%20%2F%3ECOUNTA(%E2%80%98Dash%20Source%E2%80%99!%241%3A%241))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802109%22%20slang%3D%22en-US%22%3ERe%3A%20Named%20Range%2C%20Pivot%20Table%20failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802109%22%20slang%3D%22en-US%22%3EAaaah%2C%20thank%20you!%20You%20know%2C%20all%20it%20needed%20was%20the%20%241%3A%241%20at%20the%20end%20and%20now%20it%20works!%20Very%20silly%20of%20me%20to%20forget%20that%20bit...%20Cheers!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802546%22%20slang%3D%22en-US%22%3ERe%3A%20Named%20Range%2C%20Pivot%20Table%20failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802546%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%20Just%20the%20same%2C%20INDEX%20is%20preferable%20over%20the%20volatile%20OFFSET%20for%20dynamic%20ranges.%3C%2FLINGO-BODY%3E
Will_DACB
New Contributor

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

3 Replies
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))
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!
You’re welcome. Just the same, INDEX is preferable over the volatile OFFSET for dynamic ranges.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies