Pivot Table Wizard Fails to Recognize New Rows of a Named Range

[Ed K]

Excuse me if you've already have seen this post in another discussion group,
but someone suggested I post my Excel pivot table question here.

I've been following the same routine for years, but now Excel 2007 pivot
table no longer includes new rows of a named range (i.e., an Excel database)
AFTER I update the rows/columns included in the named range I use in the
pivot table wizard.

I've seen the other posts here with =OFFSET as a solution, but that's not
solution for me. Here's what I am writing about:

I add new rows to a database defined in Excel 2007 (columns = fields, rows =
records). I then update the named range I have used for years to include
these the new rows in. I then use the named range in the pivot table wizard.
The resulting pivot table fails to recognize the new rows of the database,
even though the named range I gave the pivot table includes the new rows.

I've been doing this monthly routine for many years with all versions of
Excel, and it has worked perfectly (i.e., the pivot table uses the new
definition of the ranged name). With Excel 2007, it no longer works (i.e.,
the pivot table ignores the new rows, even though the named range clearly
includes them).

What do I now have to do differently with Excel 2007 that none of the prior
Excel versions required me to do?

Thanks.
Ed

 

[ShaneDevenshire]

Hi,

When you say database, do you mean a range in Excel? Simple solution if
that is what you mean is to ignore the range name and define the range as a
table. To do that select the data and press Ctrl+T (or Ctrl+L), then click
OK. The pivot table will use the table. As you add new data to the
database the pivot table will automatically extend.

cheers,
Shane

 

[Ed K]

Hi Shane,

That did not work. The pivot table created through the wizard fails to pick
up a number of records in the Excel database, including records that are
neither the top row or the bottom row of the defined range.

When we solve this one, I hope I'm not embarrased at how simple the fix is.
No complaints wasting hours on this one -- only a Microsoft issue, not a
world event.

Ed

 

[KAitken]

Update Table/Range of PivotTable

Hi Ed,

Try this:
Click on your pivot table. Go to PivotTable Tools - Options on the toolbar. Click Change Data Source and select your new table/range.

I hope this works for you/is what you were looking to do. I was really confused by a lot of the answers I found when trying to get my data to update with new added rows. It's not automatic, but it is pretty simple.
Cheers!


Ed Wrote:

Excuse me if you've already have seen this post in another discussion group,
but someone suggested I post my Excel pivot table question here.

I've been following the same routine for years, but now Excel 2007 pivot
table no longer includes new rows of a named range (i.e., an Excel database)
AFTER I update the rows/columns included in the named range I use in the
pivot table wizard.

I've seen the other posts here with =OFFSET as a solution, but that's not
solution for me. Here's what I am writing about:

I add new rows to a database defined in Excel 2007 (columns = fields, rows =
records). I then update the named range I have used for years to include
these the new rows in. I then use the named range in the pivot table wizard.
The resulting pivot table fails to recognize the new rows of the database,
even though the named range I gave the pivot table includes the new rows.

I've been doing this monthly routine for many years with all versions of
Excel, and it has worked perfectly (i.e., the pivot table uses the new
definition of the ranged name). With Excel 2007, it no longer works (i.e.,
the pivot table ignores the new rows, even though the named range clearly
includes them).

What do I now have to do differently with Excel 2007 that none of the prior
Excel versions required me to do?

Thanks.
Ed












You Wrote:

Excuse me if you've already have seen this post in another discussion group,
but someone suggested I post my Excel pivot table question here.

I've been following the same routine for years, but now Excel 2007 pivot
table no longer includes new rows of a named range (i.e., an Excel database)
AFTER I update the rows/columns included in the named range I use in the
pivot table wizard.

I've seen the other posts here with =OFFSET as a solution, but that's not
solution for me. Here's what I am writing about:

I add new rows to a database defined in Excel 2007 (columns = fields, rows =
records). I then update the named range I have used for years to include
these the new rows in. I then use the named range in the pivot table wizard.
The resulting pivot table fails to recognize the new rows of the database,
even though the named range I gave the pivot table includes the new rows.

I've been doing this monthly routine for many years with all versions of
Excel, and it has worked perfectly (i.e., the pivot table uses the new
definition of the ranged name). With Excel 2007, it no longer works (i.e.,
the pivot table ignores the new rows, even though the named range clearly
includes them).

What do I now have to do differently with Excel 2007 that none of the prior
Excel versions required me to do?

Thanks.
Ed