How do I add data to my existing spreadsheet so it will automatically update my Pivot Table

How do I add data to my existing spreadsheet so it will automatically update my Pivot Table. I tried everything I could think of however nothing seems to work. Please help. I'm disparate and need to finish this project for school

Thank you very much

Peggy

-------------Problems Reply------------

Excel will use absolute range (and not refresh the PT) unless you use a dynamic range; there are a couple of ways you can do this. The first is quicker.

1) Format your data as a table. Select the Table and press Ctrl + T

Now change the data source: with the activecell in the pivot table Double click the Edit data source in the Refresh group. Make sure the the range says Table1 or whatever your table is called.

The table expands as you add data to the list. However the Pivot table wil not change automatically when new data is added you may only want to do this once a week or at the end of a working day.

To update the PT click in the table and click the Refresh button in the Pivot Table menu.

To make the PT change when each new item is added could be done with a macro (as Gary's Student suggested) but I would not recommend this as it would take place after each change to data.

2) Dynamic ranges can also be created with formulas. They take a bit longer to do but I like them;try them later when you have more time.

Say column A contains Dates and we create a named range called rngDates

Choose Formulas, Name Manager and click New.

in the Name box type rngDates

In the refers to box type the formula =OFFSET(RawData!$A$2,0,,COUNTA(RawData!$A:$A)-1)

RawData! is the name of my sheet.

Column B contains a list of IDs click on New name the range rngIds and use the formula:

=OFFSET(Dates,0,1)

The offset function can be used to add named ranges for each column.

My test table has just three Column so for the table we create a range named rngTable with the formula

=OFFSET(Dates,0,0,,3)

Why bother with Named Ranges? Well they make formulas easier to write, Use the Help files when you have time and you will find them invaluable.

Regards

Peter

You could use a worksheet change event macro to refresh the tables if it detects that data has been added or modified.

Hi, what you need is to updated the Pivot table based on the new data source if yes take a look here

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

There is no new data source. I just added additional lines of data to my existing data spreadsheet.
sorry I didn't explain properly, what I meant was that adding new rows to your existing data look Debra explanation and example in the link given

NO MATTER WHAT I DO , WHEN I NAME THE DATA SOURCE AND THEN GO TO THE PIVOT TABLE, I GET AN ERROR MESSAGE THAT "PIVOT TABLE FIELD NAME "NOT VALID" iVE GONE THRU ALL THE STEPS AND DID THE OFFSET FORMULA PROPERLLY

HELP

HOW WOULD I DO THIS

Excel will use absolute range (and not refresh the PT) unless you use a dynamic range; there are a couple of ways you can do this. The first is quicker.

1) Format your data as a table. Select the Table and press Ctrl + T

Now change the data source: with the activecell in the pivot table Double click the Edit data source in the Refresh group. Make sure the the range says Table1 or whatever your table is called.

The table expands as you add data to the list. However the Pivot table wil not change automatically when new data is added you may only want to do this once a week or at the end of a working day.

To update the PT click in the table and click the Refresh button in the Pivot Table menu.

To make the PT change when each new item is added could be done with a macro (as Gary's Student suggested) but I would not recommend this as it would take place after each change to data.

2) Dynamic ranges can also be created with formulas. They take a bit longer to do but I like them;try them later when you have more time.

Say column A contains Dates and we create a named range called rngDates

Choose Formulas, Name Manager and click New.

in the Name box type rngDates

In the refers to box type the formula =OFFSET(RawData!$A$2,0,,COUNTA(RawData!$A:$A)-1)

RawData! is the name of my sheet.

Column B contains a list of IDs click on New name the range rngIds and use the formula:

=OFFSET(Dates,0,1)

The offset function can be used to add named ranges for each column.

My test table has just three Column so for the table we create a range named rngTable with the formula

=OFFSET(Dates,0,0,,3)

Why bother with Named Ranges? Well they make formulas easier to write, Use the Help files when you have time and you will find them invaluable.

Regards

Peter

Hi,

This error probably means there is some column in your source data without a header row. Check forhiddn columns as well.

Thank you for your assistanace. I made a table from the data, 1st, how do i name the Table. it seems to be called Table2. I pressed Ctrl T on the table so it was selected. I went to the Pivot table that i want to have updated. I placaed an activecell on the pivot table but under the Data menu, only Refresh button and Connections can be depressed. The only Edit is Edit links which is greyed out. I went to connections and pressed ADD then connected it with my file name but nothing happened. I never got a message about Range except when i was making the Table. What do i do now,. I am working on Vista Excel2007

Thank you

Peggy

I gott the Pivot Table to update, now I can't get the Pivot Chart to update. What do I do?

Thanks,

Peggy

Category:Office Views:8 Time:2014-06-30

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.266 (s). 11 q(s)