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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
date grouping error

Rows 1 & 2 have text in them, row 3 is the header row and row 4 is where the data starts.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,332
Messages
6,135,945
Members
449,974
Latest member
riffburn

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
Back
Top