12 month pivot table
 

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.


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

Acro,

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


http://peltiertech.com/Excel/Charts/...tml#DynoLast12


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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.


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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






--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

Acro,

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

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

Acro,

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

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

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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




--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=552411

12 month pivot table
 

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.


--
Acro
------------------------------------------------------------------------
Acro's Profile: http://www.excelforum.com/member.php...o&userid=35456
View this thread: http://www.excelforum.com/showthread...hreadid=552411