12 month pivot table

[Acro]

I would like my pivot table to display only the last 12 months of data.
Is there a setting i could change somewhere to make it only give me the
last 12 months? It is a lot of data points sum'd by month and year and
is giving me all 12 of 2005 and then 6 of 2006. I would like the six
of 2006 but only 7 and up of 2005.

 

[SteveG]

Acro,

Check out Jon Peltier's site. I used this and it worked great.


http://peltiertech.com/Excel/Charts/Dynamics.html#DynoLast12


HTH

Steve

 

[Acro]

Thanks steve i will try that! i hope that works when referencing pivot
tables for the data as well as just normal cells of data.

 

[Acro]

I do not believe this works when referencing a pivot table for data.
could not get it to work. it says i have a reference to external dat
or something like that even when i am not referencing any externa
data! that must be because i'm referencing the pivot table. Any othe
suggestions or am i out of luck

 

[SteveG]

Acro,

Off on vacation for a few days so I apologize for the late response.

Does your Pivot Table and data reside in the same workbook as your
chart? I just tried this using a pivot table whose source data & the
chart was in the same workbook and had no issues. If you have the data
in different books, they both need to be open to view the data
correctly.

HTH

Steve

 

[Acro]

Oh wow alright thanks I will attempt this again I must have just done
something wrong. The pivot tables i am trying to create the charts of
are getting data from a diff worksheet on the same workbook so it
should be good to go.

 

[Acro]

I guess i have my formula in wrong since the data it is giving me i
wrong but i have managed to get it to create a table for me. Is ther
a way to get the pivot table to NOT have totals because for some reaso
my chart thinks that the total is one of the twelve data points and i
throwing off my graph

 

[SteveG]

Acro,

I assume you mean subtotals and not row totals. Either way, right
mouse click on your pivot table and select Table Options from the menu.
To remove the subtotals, deselect the "Subtotal hidden page items".
You can also remove the Grand totals for rows and Grand totals for
columns. Make sure that your named ranges reflect the correct data as
well. (chtCats, chtVal, chtLen)

HTH

Steve

 

[Acro]

The subtotals refuse to go away. They are subtotals of the columns.
that box is not checked and they are still there. I have unchecked
about every box that i can find and they still will not go away. The
graph is grabbing mostly the right data now. I would ideally like to
reference one piece of data from the pivot table and then as the other
charted I would like to reference some data NOT in my pivot table
report since i can not figure out a way to get that data in the report.

 

[SteveG]

Do you mean the total column? Your PT has to show something in the
Total column for each line item. For subtotal, try right mouse
clicking on one of the column subtotals and select hide from the menu.
If you'd like, you can forward it to me and I'll take a look. Just tell
me what ranges you wish to have the chart reference and how.


Regards,

Steve



(e-mail address removed)

 

[Acro]

Alright i have just told it to grab the last 13 instead of 12 that way
it grabs the one blank point at the end but it does not really matter
too much! Seems to have worked. I think i have found my way around
this problem! thanks for the help steve

 

[SteveG]

Acro,

You're welcome. Glad you got it to work.

Cheers,

Steve

 

[Acro]

I am about to destroy this program! it worked on my test pivot tabl
which is an exact copy of one of the pivot tables i needed it to wor
on. I went back to my original workbook which contains three pivo
tables per page that i need dynamic charts for and it will not wor
even though i have set it up the exact same way! it still gives me th
invalid reference to external data message but in no way am
referencing ANY external data! I am very lost and saddened by thi
since i was so confident it would work

 

[SteveG]

Acro,

Ok, I just re-created your scenario without an issue. I also did i
where the 3Pivot Tables in one sheet that ARE using data from
different workbook for the source data but reside in the book where th
chart is to be placed. The other book is not open. It is also workin
without issue.

A couple of things about using the defined names is you need to mak
sure that your names are in the workbook where your chart is. If the
are not, it would cause you to get an error message. Also, whe
setting up the source data for the chart, the names need to b
preceeded by the sheet name so for your Chart Categories it should b
=Sheet1!chtCats, not just chtCats or you will get an error message.

Does that help?

Stev

 

[Acro]

Everything is in the same workbook and i have the correct sheet nam
typed in. The only thing i can think of is it has issues since i ge
the data for my pivot table from an external source. I am pulling tha
data from a difference excel sheet elsewhere but that really should no
be a problem. The program just does not like me.:confused

 

[SteveG]

Acro,

I'm not running into the same issues so I am not sure if there is
anything I could do short of looking at what you have to see where the
problem is. What is the exact error message you are getting and at
what point does it happen? Is it when you are adding a series to the
chart?

If you are able to send the workbooks to me, feel free to at the email
address below and I'll take a look.

Steve

(e-mail address removed)

 

[Acro]

I can't send you the document since it is work related and confidential.
The error message I get is "Your formula contains an invalid external
reference to a worksheet. Verify that the path, workbook, and range
name or cell reference are correct, and try again." I get this error
message after i enter in my 'worksheet name'!chtValA. I believe this
means that there is something wrong with my chtValA column and my
chtValB column. Those are decided by the chtCats column arent they?
so possibly there is something wrong with that formula. I do not
believe there is tho since i copied it exactly and just changed the
A1's into J15 since that is my "month" column and changed the rest of
the A:A into J:J. This worked for me in my trial document and it was
the exact same pivot table just copied into that workbook. It really
confuses me as to why it does not work in its own workbook but will
work in a different workbook. I noticed in the workbook that worked
when i select inside the formula for chtValA,B or the chtCats it will
have a little box around the information that it finds from those
formulas. This box does not show up around the cells the same way it
does in the working sheet. I just have no clue why since i typed the
formulas in correct! its like they are referencing cells from some
other sheet or something even though i have typed the name in
correctly.