Charting 2 pivot data items as one result

I have a pivot table that has a Count of Tools and a 
count of plants.  What I want to chart is the result of 
the Count of Tools/Count of Plants.  I tried using 
formulas from the PivotTable tools but I can't seem to 
make it work.

I hate to think that I have to write the code to read the 
pivot table and place the valuse in cells below the pivot 
table then chart that range.

Thanks for the help.


Scott
0
anonymous (74722)
12/18/2003 3:45:29 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
617 Views

Similar Articles

[PageSpeed] 22

Scott -

You wouldn't need VBA code to do this.  If you know the structure of 
your pivot table, you can put a formula in a cell or cells outside the 
pivot table to give you this value.

Make a regular chart, not a pivot chart.  You can't add data outside of 
the pivot table to the pivot chart, and you can't do a lot of formatting 
to a pivot chart, either.  But you can add pivot table data to a non 
pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Scott Stewart wrote:

> I have a pivot table that has a Count of Tools and a 
> count of plants.  What I want to chart is the result of 
> the Count of Tools/Count of Plants.  I tried using 
> formulas from the PivotTable tools but I can't seem to 
> make it work.
> 
> I hate to think that I have to write the code to read the 
> pivot table and place the valuse in cells below the pivot 
> table then chart that range.
> 
> Thanks for the help.
> 
> 
> Scott

0
12/18/2003 5:15:06 PM
I have to use a pivot table because the data comes from a 
sheet with tons of data.  In my code I create a chart, 
but it defaults to a pivot chart.  I have code that reads 
the pivot table and calculates the values I want and when 
I try setting the range into the chart source I get an 
error saying I cannot change the Pivot Data.  I don't 
want to.  Either show me what you are saying with an 
example or can you tell me how to set the chart 
sourcedata.  Either way I would greatly appreciate your 
help.


Here is my code:

arange is a type.  In a previous routine I create a range 
of data from the pivot table and save the range info.  

Sub BuildEAToolChart(ByRef aRange As ThisRange)
    Dim wksht As Worksheet
    Set wksht = Worksheets("EA Tools")
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=wksht.Range
(wksht.Cells(aRange.StartRow, aRange.StartCol), 
wksht.Cells(aRange.LastRow, aRange.LastCol)), _
        PlotBy:=xlRows
    ActiveChart.Location Where:=xlLocationAsObject, 
Name:="EA Tools"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "% Of Events with E&AT Report"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, 
xlPrimary).AxisTitle.Characters.Text = "% Events"
    End With
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScaleIsAuto = True
        .MaximumScale = 1
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    Selection.TickLabels.NumberFormat = "0%"
End Sub





>-----Original Message-----
>Scott -
>
>You wouldn't need VBA code to do this.  If you know the 
structure of 
>your pivot table, you can put a formula in a cell or 
cells outside the 
>pivot table to give you this value.
>
>Make a regular chart, not a pivot chart.  You can't add 
data outside of 
>the pivot table to the pivot chart, and you can't do a 
lot of formatting 
>to a pivot chart, either.  But you can add pivot table 
data to a non 
>pivot chart.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>http://www.geocities.com/jonpeltier/Excel/index.html
>_______
>
>Scott Stewart wrote:
>
>> I have a pivot table that has a Count of Tools and a 
>> count of plants.  What I want to chart is the result 
of 
>> the Count of Tools/Count of Plants.  I tried using 
>> formulas from the PivotTable tools but I can't seem to 
>> make it work.
>> 
>> I hate to think that I have to write the code to read 
the 
>> pivot table and place the valuse in cells below the 
pivot 
>> table then chart that range.
>> 
>> Thanks for the help.
>> 
>> 
>> Scott
>
>.
>
0
anonymous (74722)
12/19/2003 6:21:48 PM
I fixed it by setting a range to the "aRange" parameters, 
then I called the Charts.Add.  This allowed the chart to 
be referenced to the selected area of the page.  I was 
able to set the sourceData and it worked fine.

Thanks for the help

>-----Original Message-----
>I have to use a pivot table because the data comes from 
a 
>sheet with tons of data.  In my code I create a chart, 
>but it defaults to a pivot chart.  I have code that 
reads 
>the pivot table and calculates the values I want and 
when 
>I try setting the range into the chart source I get an 
>error saying I cannot change the Pivot Data.  I don't 
>want to.  Either show me what you are saying with an 
>example or can you tell me how to set the chart 
>sourcedata.  Either way I would greatly appreciate your 
>help.
>
>
>Here is my code:
>
>arange is a type.  In a previous routine I create a 
range 
>of data from the pivot table and save the range info.  
>
>Sub BuildEAToolChart(ByRef aRange As ThisRange)
>    Dim wksht As Worksheet
>    Set wksht = Worksheets("EA Tools")
>    Charts.Add
>    ActiveChart.ChartType = xlLineMarkers
>    ActiveChart.SetSourceData Source:=wksht.Range
>(wksht.Cells(aRange.StartRow, aRange.StartCol), 
>wksht.Cells(aRange.LastRow, aRange.LastCol)), _
>        PlotBy:=xlRows
>    ActiveChart.Location Where:=xlLocationAsObject, 
>Name:="EA Tools"
>    With ActiveChart
>        .HasTitle = True
>        .ChartTitle.Text = "% Of Events with E&AT Report"
>        .Axes(xlValue, xlPrimary).HasTitle = True
>        .Axes(xlValue, 
>xlPrimary).AxisTitle.Characters.Text = "% Events"
>    End With
>    ActiveChart.Axes(xlValue).Select
>    With ActiveChart.Axes(xlValue)
>        .MinimumScaleIsAuto = True
>        .MaximumScale = 1
>        .MinorUnitIsAuto = True
>        .MajorUnitIsAuto = True
>        .Crosses = xlAutomatic
>        .ReversePlotOrder = False
>        .ScaleType = xlLinear
>        .DisplayUnit = xlNone
>    End With
>    Selection.TickLabels.NumberFormat = "0%"
>End Sub
>
>
>
>
>
>>-----Original Message-----
>>Scott -
>>
>>You wouldn't need VBA code to do this.  If you know the 
>structure of 
>>your pivot table, you can put a formula in a cell or 
>cells outside the 
>>pivot table to give you this value.
>>
>>Make a regular chart, not a pivot chart.  You can't add 
>data outside of 
>>the pivot table to the pivot chart, and you can't do a 
>lot of formatting 
>>to a pivot chart, either.  But you can add pivot table 
>data to a non 
>>pivot chart.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>http://www.geocities.com/jonpeltier/Excel/index.html
>>_______
>>
>>Scott Stewart wrote:
>>
>>> I have a pivot table that has a Count of Tools and a 
>>> count of plants.  What I want to chart is the result 
>of 
>>> the Count of Tools/Count of Plants.  I tried using 
>>> formulas from the PivotTable tools but I can't seem 
to 
>>> make it work.
>>> 
>>> I hate to think that I have to write the code to read 
>the 
>>> pivot table and place the valuse in cells below the 
>pivot 
>>> table then chart that range.
>>> 
>>> Thanks for the help.
>>> 
>>> 
>>> Scott
>>
>>.
>>
>.
>
0
anonymous (74722)
12/19/2003 7:59:04 PM
Hi All,

      I also have the same issue..

       I have a query on Pivot Table and pivot chart. In our application, We draw a pivot table for which a chart also is displayed .I use the same process as in MS BI Portal.

I need to show some extra fields in the pivot chart which will not be displayed in pivot Table like I have Product sales in pivot table but sales, profit will come in Pivot chart.Is it possible.

  I supply XML for Chart separately and Pivot Table separately.. And when user changes the Chart I have to check the present XML of Pivot chart and change the Pivot Chart format also accordingly. I hope to capture one event (Query..?)

  I think there is another approach also.I just draw two Pivot tables.One with the Table's requirement.Other with The chart requirement. and make the dummy Pivot table (created just for chart ) as source of Pivot table..and make this dummy one as invisible..(Is it possible..Once I assign the data source THe visibility property does not work..???)

Thanx
Jegan

 

Is it possible..Plz throw some light


0
anonymous (74722)
1/9/2004 9:46:15 AM
Reply:

Similar Artilces:

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Goal Seek #2
Can anyone tell me a little about goal seek? Hi basically it's used to change an input variable so that the result of the formula equals what you want it to ... say i have the following A B C 1 10 20 =A1+B1 now C1 will give me 30, but if i want to know what B1 will need to be if i want C1 to show 50, i can use goal seek Set C1 to value of 50 by changing B1 Hope this helps Cheers JulieD "Jamie Hart" <jhart@emaple.net> wrote in message news:u3OscELdEHA.244@TK2MSFTNGP12.phx.gbl... > Can a...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

unable to paste Excel 2003 chart into Outlook 2003
(This was posted on "excel.charting" group.) I have a user who's unable to paste an Excel 2003 chart into Outlook 2003 email message. In Outlook options, the checkbox is selected for "Use Microsoft Office Word 2003 to edit e-mail messages". When I tested this on my own computer running the same version of Office, if the box is check, I have no problem pasting; if this box is cleared, I cannot paste. But on his computer, it doesn't work regardless. Thanks and regards, TL ...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

get a result of an sql into a field
Hi there I would like to get a result of an sql execution (ms sql server) into aq filed. example i A1 I have a ID number in A2 I would like to get the result of something like this 'select name from address where id=A1' Does this exist in Excel ? Thanks in advance Ralf Here is the sub i have written for loading an Sql Query into th worksheet. Parameters: Server Name DataBase Name SQL Command Target Sheet name Column to begin from Row to begin from ex: CALL LoadData("MyServer","MyDataBase","Select UserName fro TblNames", "QueryData"...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

Pivot Table Question #5
How do I make the row headers show up in front of each row on pivot table instead of just once on the first row of a section? Thanks Try this: Copy the pivot table Do a Paste Special > Values into another sheet Ensure that the top left cell is A1 Run the Sub FillBlanks() below (from MVP Debra D) Sub FillBlanks() 'by Debra Dalgleish 7-Dec-2001 'fill blanks cells with data from above Range("A1").CurrentRegion _ .SpecialCells(xlCellTypeBlanks) _ .FormulaR1C1 = "=R[-1]C" Range("A1").CurrentRegion.Copy Range("A1").PasteS...

Contacts in Exchange #2
We are running Exchange 2003 and Outlook 2003. I have tried to give a user rights to her boss's contacts through delegation and setting the rights on the contacts folder, however, while she has the rights she is unable to add or see her boss's contact folder in her Outlook. She can see it if she runs a search, but can not permanently put it in her list of contact folders. IS there a way that I can accomplish this through the back-end? Thanks, Joseph rapoport jrapoport@insurmark.net ...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Object pivot point
Is there a way to change the point at which an object will rotate? Or will it always rotate about the center? For instance, I would like a square to rotate about each corner. Thanks, Dave B You really need to invest in a drawing program. Serif has a free offering, DrawPlus 4. If you want the full feature version 6 it is $10.00. I use CorelDraw, it is simply a matter of moving the pivot point to the corner. I can't be sure Serif has this feature. I do know Publisher does not. You can use ruler guides to place your object and use the "format autoshape" to control the amount ...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

how do I add error bars to a 3D chart in excel?
The help states you can only add error bars to data series in 2D area. Is there a way to add them to a 3D chart? Hi, I would not have thought so. Obviously as it is not a built-in option the only way would be a work around perhaps using dummy series. Unfortunately you can create 3d combination charts. Stick with the 2d view. Cheers Andy elahe wrote: > The help states you can only add error bars to data series in 2D area. Is > there a way to add them to a 3D chart? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info I checked, and error bars are not offered for 3D ch...

Default scene is solid blue but only on one account
I have 2 accounts with WLM, on one the default scene is as it should be, a mix of blue and white. But on the other account it is solid blue. If i change the scene in the program it changes to that but any contact windows i open continue to be solid blue by the persons name but above that is the new scene. I've done a repair job on 2 computers but does not fix the problem. Guesses? ...

Sent emails not logged in Sent Items (Outlook 2010)
I am using the Beta version of Outlook 2010 with Windows 7 Pro (64 bit). Sent emails are not logged in the Sent Items folder nor do saved drafts appear in the Drafts folder. I have confirmed that the relevant settings are checked in the Mail Settings. Any ideas what I can do to solve this problem? -- Stephen Newton "snewton" <snewton@discussions.microsoft.com> wrote in message news:F7345EA6-9E42-4DF7-AFA5-AD2DF2CA840D@microsoft.com... >I am using the Beta version of Outlook 2010 with Windows 7 Pro (64 bit). > Sent emails are not logged in the Sent I...

Items in this message are still loading. Please wait a moment and try again.
Get this error message when trying to print an HTML email using Outlook 2002 10.6515.6735 SP3. I've seen lots of single post threads regarding this issue with no responses. The problem is, the moment lasts up to an hour. Are there any settings that can be tweaked to speed this process up? Could it possibly be a printer issue? This just recently started happening. Any suggestions are appreciated. I haven't ever experienced this but are you on a slow Internet connection? An hour is an awful long time (which I'm sure you know already) "Geoff" <geoff.warner@gmail...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...