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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.