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,218
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,122,630
Messages
5,597,264
Members
414,133
Latest member
lucid33

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