Create pivot table from dynamic range (no VBA)?

EdNerd

Active Member
Joined
May 19, 2011
Messages
416
(Windows 10, Office 2013 and 2016)

I am new to Pivot Tables. I used the built-in wizards to create a pivot from a named table with a static range. Unfortunately - but I should have expected it, when I tried to insert a row of data in the middle of the table, I got an error that said I couldn't do that because it would change my table. I'm assuming that's due to (A) having a static defined range, and (B) having a connected Pivot Table??

So I thought if I simply used a dynamic range as the data source for my pivot, all should be good. My data is on a sheet named EXPENSES, from columns A to G; headers in row 1, data currently down to row 37. All my column headers are single word, with no spaces, numbers, special characters, or punctuation. I can define the table MyExpenses with RefersTo formula =OFFSET(EXPENSES!$A$1,1,0,COUNTA(EXPENSES!$G:$G),7).

On a sheet named CHARTS, I select B2 and attempt INSERT >> Pivot Table. But when I enter MyExpenses for the Table/Range, I get this error:
"The PivotTable field name is not valid. To create a Pivot Table report, you must use data that is organized as a list with labeled columns. If you are changing the name of a Pivot Table field, you must type a new name for the field." I also tried entering the OFFSET formula directly as the Table/Range, but got "Data source reference is not valid."

Three notes:
1. I'm at work, and IT does not allow add-ins, so I can't use the XL2BB add-in to share my data. I have included a screen capture of most of my range.
2. Because this is being created for others who for reasons unknown want it "this way!", columns F and G would normally be hidden. The user would complete columns A through E (C and D are Data Validation drop-down lists, and then if the AMOUNT value is to be considered income, puts a Y in column I; formulas then pull the value into either F or G. Column H is blank and hidden to create a buffer between the range/table data and everything else.
3. This is a test book, so I simply copied the existing entries and entered the Y down column I. A real workbook would not have duplicate entries and values - hopefully!!

DynamicPivot.PNG

When using the search for pivot tables with a dynamic range, every answer used VBA. I'd rather try to stay away from VBA for this one. Can someone please drop-kick me in a good direction for this one??
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,088
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your range should include the header row, and therefore be:

=OFFSET(EXPENSES!$A$1,0,0,COUNTA(EXPENSES!$G:$G),7)
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,959
Messages
5,767,327
Members
425,404
Latest member
Bairkus

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