Help with Dynamic Range and Pivot Table

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
I am using the following named range to selet the desired data to represent in a pivot table.

=OFFSET('MISO SUMMARY'!$A$7,0,0,COUNTA('MISO SUMMARY'!$A:$A),6)

The data starts in A7 and goes to column F. When I select the range and it is highlighted, there are 4 extra rows that are blank that are included in the range. I think this may be throwing off my pivot table becase if I create a pivot table using the named reference, I cannot group any information. However if I manually define my range, the pivot table works fine.

Any ideas?
Matthew
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Matthew

Do you have any formulas in column A that are maybe getting counted with COUNTA?
 

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
Column A contains a date. Columns B-F contain VLOOKUP statements that return values from other sheets within the same workbook based on that date.

For testing, I moved the data up to cell A1 and the dynamic range worked correctly?

I am problably going about this all the wrong way. The chart that I am generating the pivot table from is a summary to other sheets. I should probably be creating the pivot table directly from the data on the multiple sheets, but I have not had much luck with that either.

Matthew
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,452
Members
412,595
Latest member
slim313
Top