Need help with my Pivot Table giving me referernce error.

I updated the source data and now I can't get my data to refresh. I am getting an error message that gives me a reference error. I also want to the MTD Thru Feb 23, 2015 with the entire Feb data as well as add March.

I would also like to add the PO reasons to the Pivot table and wondering where it would send to add the data.
Order-Entry-Tracker-032515.xlsm
Marcia MorrisProject ManagerAsked:
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,

you have to hange the source table (named range myTable) to

=OFFSET('SOURCE DATA'!$A$1,0;0,COUNTA('SOURCE DATA'!$A:$A),13)

Regards
Order-Entry-Tracker-032515V1.xlsm
0

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
Marcia MorrisProject ManagerAuthor Commented:
Rgonzo1971 thank you again...I don't know what the heck I did that messed up the source table that you helped me with before. I have a couple of additional questions.
1. Is there a way I can add the order count (ie., on chart 37-Orders for Jan) as part of the Pivot so that I don't have to manually add it to the Chart each time I run this report? Right now I have a tab 'order quantity' that I refresh so that I know what the order count on the Chart should be.

2. How do I make the bars in the chart closer together so that the legend isn't' overlapping on the Mar2015 FP bar?

I want to understand the OFFSET formula. What exactly is it saying? I know you have it now pulling all data automatically because before it wasn't with whatever I had in there previously. I just want to make sure I understand the formula a little bit more.
Order-Entry-Tracker-032615.xlsm
0
Rgonzo1971Commented:
Hi,

2. You can change the Chart area click in the chart but not an element

1. Nope

3.=OFFSET('SOURCE DATA'!$A$1;0;0;COUNTA('SOURCE DATA'!$A:$A);13)

COUNTA('SOURCE DATA'!$A:$A) counts the cells that are not empty

=OFFSET('SOURCE DATA'!$A$1;0;0;COUNTA('SOURCE DATA'!$A:$A);13)
'SOURCE DATA'!$A$1
 The reference from which you want to base the offset

0
The number of rows, up or down, that you want the upper-left cell to refer to.Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
0
The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).

The result of count
The height, in number of rows, that you want the returned reference to be.
13
The width, in number of columns, that you want the returned reference to be. Width must be a positive number.


Regards
0
Marcia MorrisProject ManagerAuthor Commented:
thank you very much
0
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
Spreadsheets

From novice to tech pro — start learning today.