Error in my OFFSET defined name range in my pivot

[yellowcabguy]

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)

 

[venkat1926]

sorry ignore the posting.

 

[GorD]

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]

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]

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]

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]

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]

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]

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