Pivot not updating problem

I am having a problem with pivots where if a new items comes in it is not being picked up even after a refresh. I am using a formula in Name Manager like:

=OFFSET(Bals!$A$1,0,0,COUNTA(Bals!$K:$K),11)

and when I check range it is picking up the full range.

When I go into the pivot and filter I can see the new item but it is NOT ticked.

Is there a way to ensure that all new items that come into the pivot will be picked up and the box ticked?

Thanks
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

If you add a new column(field), it won't be ticked automatically

but you can have an updated range with

=OFFSET(Bals!$A$1,0,0,COUNTA(Bals!$K:$K),COUNTA(Bals!$1:$1))

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Hi Rgonzo

it's not a new column or field it is just another row, so like this

853913      5      GBP
872836      8      USD

both GBP and USD are included in the pivot but when a new [currency] item [that has not previously been in the pivot] comes in it does not get included

so the item JWK would not be included in pivot

853913      5      GBP
872836      8      USD
872836      3      JWK
Rgonzo1971Commented:
Could you send a dummy?
Saurabh Singh TeotiaCommented:
Jagwarman,

In the formula you are using this part of the formula plays the key role in number of rows to be picked up..

=COUNTA(Bals!$K:$K)

Now if your K Column has blanks what i mean is that let's say you have data from row 1 to row -25 and now row-5,10,12 are blank..now instead of going till row-25 it will go till row-22 since counta will give 22 as answer..

You need to change to the column which will have data filled in all rows assuming if that is A or B Column then you can use something like this...

=COUNTA(Bals!$A:$A)

Saurabh...
JagwarmanAuthor Commented:
it's working now
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.