Error in my OFFSET defined name range in my pivot

yellowcabguy

Active Member
Joined
Dec 28, 2005
Messages
440
I am experiencing a problem in grouping dates in my pivot chart and am wondering if it has somethign to do with the defined name range I created to aid in refreshing the table when I add more data. Is it possible that the "65536" in the formula is causing the problem??


=OFFSET(Inventory!$A$3,0,0,COUNTA(Inventory!$A$3:$A$65536),90)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
I don't think its anything to do with the named range.


Although I would change that anyway(unrelated) to

=OFFSET(Inventory!$A$3,0,0,COUNTA(Inventory!$A:$A)-2,90)


Does the range need to be 90 columns wide?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi GorD

Why would you amend the range to COUNTA(A:A)? If there's no text within A1 and A2 you'll be picking up the wrong range.

Richard

EDIT: So maybe:

=OFFSET(Inventory!$A$3,0,0,COUNTA(Inventory!$A:$A)-COUNTA(Inventory!$A$:$A$2),90)

But I'm still not convinced it's necessary
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

Hi Richard, i should have pointed out that the -2 needed to be adjusted and I had assumed that A1 and A2 contained Header data. If A1 and A2 are blank drop the -2.

Maybe just my personal preference
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Fair enough :)

I must admit, my personal preference on data sheets is to have only headers (in row1) and data below it, and nothing else (no totals formulas at the bottom - move these to another sheet), in which case I would always use your formula structure (without the -2 obviously!).

Richard
 

yellowcabguy

Active Member
Joined
Dec 28, 2005
Messages
440

ADVERTISEMENT

date grouping error

Rows 1 & 2 have text in them, row 3 is the header row and row 4 is where the data starts.
 

yellowcabguy

Active Member
Joined
Dec 28, 2005
Messages
440
offset error

I deleted rows 1 & 2 which contained things like TODAY and some totals so that now Row 1 is the header and row 2 is where the data begins. This is the named range I am using. The 90 is the # of columns in the data sheet.

Still does not work.

Could it have to do with how the date is being stored?? I have it formated as date but these fields are used to calcuate other fields (compare one field to another to deternine age) and maybe that has somethign to do with it?


This is the range I am now using


=OFFSET(Inventory!$A$1,0,0,COUNTA(Inventory!$A:$A),90)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Other fields shouldn't affect the Grouping. If you want, you can send me your workbook and I'll have a lok at it. Obviously remove any confidential data first, and watch out whether in so doing your problem disappears.

I've sent you a PM.

Richard
 

Forum statistics

Threads
1,136,712
Messages
5,677,330
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top