Refreshing Pivot table data

Hi,

I have a reoccurring problem that I can't figure out...
I have a file with data per client per country that i wanted to setup in a pivot table that I can refresh when i insert new data. For example let's say this is the data I start with:

client country amount
a canada 100
a norway 100
b norway 100
b sweden 100
c greece 100
c sweden 100
d greece 100
d norway 100

Then this will be my pivot table:

Row Labels Sum of amount
a 200
b 200
c 200
d 200
Grand Total 800

What happens after that is that for each client I add new countries with their amounts and update the old amounts, but when I refresh the pivot table it only updates the amounts of the original countries if they we're changed, but the new countries aren't included in the table. How come that happens and is there any way to fix it?

Thx!

I've attached an example of data I'm using and how I create my Pivot, the data is a report I pull from a system that I need to pull on a regular basis as the numbers change daily.
So when I pull a new report i delete all data and paste the new report instead of it, then refresh. Then as I said before - if there's a new country in the report that wasn't there before, it would calculate it in the report, it will only update the amounts on teh already existing countries...

Sorry I meant to write: if there's a new country in the report that wasn't there before, it won't calculate it in the report

I see that it works but I'm not sure how. When i try to repeat what you did it doesn't work for me...how did you add those new countries?

Originally Posted by yael

I see that it works but I'm not sure how. When i try to repeat what you did it doesn't work for me...how did you add those new countries?

that was meant for DILIPandey

Originally Posted by Richard Buttrey

Hi,

See the attached. I've created a dynamic range name called 'Data' and added a dummy new country at the bottom. As you add new countries or clients and click refresh you'll find the Pivot Table updates automatically.

Note that the range name relies on their being a value in every row of column C. If that won't be the case and either column B or C will always contain a value then just alter the COUNTA() bit of the range name to reflect that.

Regards

How did you create the dynamic range though?

Originally Posted by Richard Buttrey

Look in Formulas Name Manager.

You should get into the habit of using range names all the time, be they straightforward names covering specific cells or ranges, or as in this case a name that automatically expands as new data is added.

Regards

I think I got it, thanks for your help!