New to Pivot tables - need help updating it. 1

[mrichelle]

Hi I was just wondering if there is a way to update a pivot table. I have data in for the month of march and need to input for the month of april. I am fairly new to pivot tables and every thing I know to do is not working (which may mean i do not know what I am doing). When I click on the data field to change the data, I get "cannot change this part of a pivot table" and i dont know what to do. Also I have just inherited this spreadsheet, and having to update it. Thanks for all help in advance!

 

[yogia]

Hi mrichelle:

Here is how I would do it ...

1) I will add the April entries to the database associated with the PivotTable
2) Then I will click somewhere in the PivotTable
3) Then in the PivotTable ToolBar, I will click on the red colored exclamation point to update the PivotTable.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[elsenorjose]

mrichelle,

yogia's post will work but only if you've defined the database via a named range. in other words, if the pivot table was created with a cell reference, meaning say, cell A110000 or something contain the data, adding data after D10000 and refreshing will do nothing. you would have to redefine the data range. This link will take you to a great reference on dynamic data sources.

http://www.contextures.com/xlPivot01.html

If you set up your data that way, then yogia's solution will work anytime you need to add new data.

 

[yogia]

Hi mrichelle:

To add to what elsenorjose has stated, if in the original database, you make your entries by inserting more rows and columns within the confines of the starting-row-and-column and the ending-row-and-column of the database, your database would be automatically expanded to cover all of the data including that in the additional rows and columns -- boy that sounds like a mouthful.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[mrichelle]

Ok I am trying really hard to understand what you are saying and I know you guys are giving me good advice, but i am so clueless because I have never worked with pivot tables. Let me explain better maybe that will help. I currently have data for March 07 and March 06 in a pivot table. I need to get rid of March and now have April 07 and April 06 only in the table. Also it is only like 10 rows of data. I guess the person who created it did not want to key in the data automatically, not sure, but i think it would have been easier. But, I guess what the table is doing is calculating a total as a "check point". So, would I still do the same steps as you guys suggested, and if so, can you break it down in non experienced pivot table user terms? Thanks.

 

[elsenorjose]

Check out the section on Pivot Table Basics in the link I provided. You might need more than a few tips from Tek-Tips to get you started Not trying to NOT help you but much of the advice provided in these forums assumes some level of knowledge and you state you are 'clueless'.

All data in a pivot table is driven by SOME data source, typically another tab in the workbook with the data. What we are suggesting is updating that tab and then refreshing the pivot table. You typically don't 'key in' any data since you usually use a pivot table to summarize thousands of rows of data. Also, not sure what you mean by 'check point'. Could you elaborate more?

Sorry I can't help more. Maybe one of the gurus will step in soon!

 

[SkipVought]

Hi,

Select in the PT, and activate the PT wizard.

Click the BACK button. Now the Data Source Range is displayed. You can from here adjust the range to include ALL rows of data. That's the HARD way, but it's a way.

Here's one of the ways that I use. Not in the PT, select the sheet with the source data.

Make sure that the data table is ISOLATED, that is, data that is not part of the source data is NOT adjacent to the table at any point.

Pick a column in your data that has and will ALWAYS have a value in every row of the source data. There must be no data ABOVE or BELOW the table in that column. There must be no data in the ROW containing the table headings to the right of the table.

Insert/Name/Define - Opens the Define Name window.

In the Names in Workbook: textbox, enter a name that will define your source data. Database is a good name or maybe dbMyData

In the Refers to: textbox enter and follow these instructions...
[tt][highlight white]
=offset(
[/highlight][/tt]
then select the top left cell in your table
[tt][highlight white]
,0,0,counta(
[/highlight][/tt]
then select the COLUMN that you picked above
[tt][highlight white]
), counta(
[/highlight][/tt]
then select the HEADING ROW
[tt][highlight white]
))
[/highlight][/tt]
You should end up with a formula that liks similar to this...
[tt][highlight white]
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
[/highlight][/tt]
Here's what the OFFSET fuction does.
Argument 1: The anchor reference for the range
Argument 2: The ROW offset for the range to start (in this instance NONE)
Argument 3: The COLUMN offset for the range to start (in this instance NONE)
Argument 4: The NUMBER OF ROWS in the range (the count of data items in the column)
Argument 5: The NUMBER OF COLUMNS in the range (the count of data items in the row)


Skip,

_[red][/red]

 

[SkipVought]

oops,

Hit Add and Close

Select the PT. Activate the PT Wizard, BACK button. Replace the range reference in the Range textbox with the Range Name you entered in the Names in workbook, like...
[tt]
=Database
[/tt]

Skip,

_[red][/red]