Pivot Table :: How To Get The % Data Above A Particular Value
I have a excel sheet with following 4 columns
Division Name:
Location Name:
Transformer capacity:
Transformer earth resistance:
Now how can I get answer of following queries in Pivot table. (Excel 2007)
1. % of a particular capacity of transformers in a division say % of 400 capacity transformers in all divisions.
2. % number of transformers having Transformer earth resistance value above a particular value in all divisions. Say % of Transformers having Transformer earth resistance above 2.6 in each division.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Import Data From Access Table To Pivot Table - Enable Auto Refresh
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access). Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest) The problem is i will get "....A file name already exist...do you want to overwrite.." prompt. Which defeat the automate process. Any other solution to enable the automatic refresh on open the excel workbook? Or Access can overwrite the exist file or save it as another file name with timestamp ?
View Replies!
View Related
Pivot Table An Extract Of Each Data Contained In This Table
i have a pivot table an extract of each data contained in this table. [img]Count of NAMdate SERVICENAM12-oct10-déc11-décGrand Total Commercial-lauralaura11 Commercial-laura Totalgh11 custody-jonathanjonathan112 k11 custody-jonathan Totalgh1113 settlement-ludovicludovic11 settlement-ludovic Totalgh11 SPQC-elodieelodie112 SPQC-elodie Totalgh112 Grand Total1337
View Replies!
View Related
Change The Date On One Of The Pivot Table And Pivot Table Match
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View Replies!
View Related
Pivot Table That Get Data Dynamically
I wonder if is it possible to get data in the pivot table dynamically? For example, I do insert data through the userform [ new department] and when I run the report I see the new entered data in the pivot table. will I have this new department title in the list box in the pivot able?
View Replies!
View Related
Pivot Table To Sort Data?
I need to format some information in an Excel file, before exporting it. All the pivot table information I have found online is primarily for account management, using counts and sums. The data is currently in this format: DEV#WIRE#SWITCH-1A123AMP-1A124AMP-1A125AMP-1A126SWITCH-2A127 I need to sort it into this format in another sheet or another area in the same sheet: DEV#WIRE#1WIRE#2WIRE#3AMP-1A124A125A126SWITCH-1A123SWITCH-2A127
View Replies!
View Related
Pivot Table Uses Raw Data
I'm using Excel for bookkeeping and balancing a budget. I've created one sheet for all my raw data and the other is to summarize the data using a pivot table. On the raw data sheet I have labeled my columns for the pivot table. I was hoping that in the pivot table I can select a year and have all the months for that year and data of that year be available, but not other years. I was also trying to have only the month selected of a year selected and that data be available. I also wanted to show an accounting of money spent by item. Instead, I have a pivot table that is confusing and very unattractive. The example of this will show my limited knowledge in pivot tables. I'm hoping some of you more affluent "guru" members may be able to help me get something usable and presentable. I tried creating a dynamic named range for the pivot table "BookKeeping" and a dynamic named range for the money out and money in "Accounting", but I'm not sure if I did that right. I did this because more columns will be added over time. This is the reason I'm asking the above two paragraphs. Should I be using filters some how? The Summary Sheet also has the balance of available funds that I'm somehow looking to include in this pivot table. Can the pivot table keep a running balance?
View Replies!
View Related
Pivot Table - Only Use Data If Between 2 Dates
I have a checkbook register set up in Excel, and I really like it. I thought it would be good to set up a pivot table to organize all of the entries into an expense report. I have done this for over a year now, and I love everything about it, but here is the problem: Now that I am getting so many entries, I thought it would be a good feature to limit the pivot table to a date range. I created 2 entry cells at the top of the pivot table to put a start and end date. I would like the pivot table to compare the entries in the register to the dates, and only include the entries between those dates. How do I do this?
View Replies!
View Related
Aggregating Data Without A Pivot Table
I am trying to create a formula that will aggregate different levels of data without using a pivot table. For example, i would like for my formula to return the summarized sales of Region 1. I don't want to use the sum formula because I might want to summarize by common division, such as Telecom. To do this I would at least need an input cell to define the level of detail RegionDivisionSales Region 1Telecom4291 Region 1Devices1032 Region 2Telecom2054 Region 2Devices3952 ive tried using different combinations of IF, VLOOKUP, and SUM statements, but nothing is working yet.
View Replies!
View Related
Grouping Data In A Pivot Table
from the Forum on building a macro and was kindly told that a Pivot Table would do the job better, which it does. However, my table has a column of several dates, I was wondering the best way to group the dates into months rather than showing the full date, eg, September instead of 16/09/08 or August instead of 01/08/08.
View Replies!
View Related
Pivot Table Source Data
I was wondering if anyone knows how to change the souce data for a Pivot Table? Eg if I have the Pivot table looking at Columns A & B lines 1 - 20 and I want the Pivot to also include Column C and lines 21 - 50, how would I do this? for a chart I can just right click and select the "Source Data" option but it seems that this is not available for Pivot Tables.
View Replies!
View Related
Summarize Data In Pivot Table
I have a worksheet with several columns of data that consists of month to date billing, cost, profit, etc. that are tied to job numbers. I currently have code ( AdvancedFilter) that seperates this data judging by the first two numbers of the job number. For instance it will take all job numbers that start with 11, copy them to a worksheet, print it and then do the same for 12, 13, 21, etc..
View Replies!
View Related
Show Pivot Table Data By Name
I have a list of Insurance payers - 20 or so. They are listed in a Pivot Table on sheet2 with rate data and such. On sheet1 I have a listbox with the Payers listed and can be multi selected by the user. I wish to have the user select some payers in the listbox, goto sheet2 and view the Pivot Table only containing those Payers selected from the listbox.
View Replies!
View Related
Pivot Table Retaining Old Data
I made a spreadsheet and used pivot tables to summarise the data by project numbers (Row field of pivot table). I deleted the orginal data and copied in data from another spreadsheet. When I look at the detail in row field (click on the down arrow of the project button in the row field of the pivot table) it still shows the original project numbers plus the new project numbers. The data displayed in the pivot table looks like it only refers to the new projects but is not correct. Any ideas why the old project numbers still appear? Refreshing the data doesn't help. The only way I can get the correct data is to delete the pivot table and then recreate it.
View Replies!
View Related
Pivot Table Getting Data From Different Datasources
I just recently started programming in macros for my new job and I really enjoy it. But recently I am faced with a problem that I can't find the solution to. Here's what I am trying to do: I want to make a Macro that will make a pivot table from certain column values. I will be running the macro in various Excel files with exact same columns. I m using an inputbox to get the range for the datasource and another inputbox to get the name of the datasource as well. Dim MyRange As Range Dim strTablename As String Set MyRange = Application.InputBox _ (Prompt:="Highlight entire table", Title:="Range for Pivot table", Type:=8) strTablename = InputBox("Input Name of this Table exactly as it is now") So my question is how can I get the Range and String recieved from the inputbox to make the pivottable. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ ................
View Replies!
View Related
Pivot Table For Changing Data
I'm creating a pivot table based on a section of data that will continually be of variable size. I'm trying to think of a way to command VBA to create a pivot table without giving an absolute reference to the table's source data. I can automatically select all of the data regardless of size, so I'm trying to see if I can command Excel to create a pivot table based on "Selection" instead of an absolute reference, in much the same way I might use Selection.Name instead of specifically defining a name's parameters. This is the text I currently have in VBA for the pivot table. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "End!R1C1:R424C14").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 My current idea is to name the range (a task I *know* how to do relatively) and refer to the named range instead of the RC:RC jumble.
View Replies!
View Related
Repeat Data In Pivot Table
I frequently use a pivot table to extract summaries from a large data set and then use the pivot table output itself as a data table for further analysis. Where a data value in field 1 has many corresponding values in field 2, the field 1 value appears only in the first line and the field 1 cells for the remainer of the rows below appear as blank. Currently, I copy the pivot table and paste values elsewhere and populate the blank cells with the value above in order to get a complete data set. I would like to find out if there is any way the pivot table can be configured so that where a field 1 value has multiple corresponding items in field 2, each of the cells in field 1 would repeat the relevant value.
View Replies!
View Related
Pivot Table Refresh To Include New Data
I have an existing pivot table that is feeding from data in another sheet. The data gets updated every month and sometimes their are more columns of data than the previous month. Everytime there are more columns they are not added into the pivot table when i refresh the table even though they are included in the range. Is there are way the pivot table will add these new columns when i refresh the table without me having to manually drag them in?
View Replies!
View Related
Pivot Table Data Size Differs
I have all of my pivots all set up and all of my charts running from them. My challenge is in pasting in the new data every day into the master Sheet BECAUSE, in order to group the dates (from format 20/02/08 into Month and Years in the pivot) you have to 'WRAP' the data, instead of simply blocking in all of the columns...... The challenge is that everyday, the data size differs, e.g; one day it might be 2000 rows, and the next 22,000 rows. SO,,,,, BECAUSE I HAD TO 'WRAP' THE DATA (IN ORDER TO GET THE DATE FORMAT I NEEDED) >>>>> HOW DO I GET THE PIVOTS TO CALCULATE ONLY USING THE CHANGING AMOUNT OF ROWS
View Replies!
View Related
Consolidate Data Into One Worksheet/pivot Table
I have a 208-tab workbook (4 employees with 52 submitted weekly timesheets). I need to consolidate and pivot job costs based on these submitted timesheets. There are two kinds of jobs: a list of products, from which the employee chooses through a drop-down list. There are also "Tracking Job ID" codes that are numbers between 7000 and an unknown ending number, which the employee types into the sheet. In both cases, the % of work performed is multiplied by the employees hourly rate and creates a total. I need a pivot table that collects these totals from all the sheets. I tried to use a VBA template from contextures.com just with a few examples from my workbook, but I don't know how to use VBA, so I failed. The only thing I did was substitute their example sheets with a few sheets from my workbook and re-wrote that part of the array formula. see attached workbooks.
View Replies!
View Related
Re-Post - Data Repeats In Pivot Table
I don't believe my earlier post was a duplicat thread. I posted the original thread, then posted a LINK to the original thread. I didn't even ask the question in the link, so there was no way someone would have wasted time answering the question from there. I didn't think I was breaching protocal. Anyway, since both instances of the thread were closed, here is the re-post. I've recently been trying to learn a little about pivot tables. In the example attached, I have 2 worksheets. One holds raw data collected from suppliers, the other holds a default pivot table created from that data.
View Replies!
View Related
Pivot Table Data Source After Email
why the data reference for a pivot table is changing after I email the workbook to a client. The source data and the pivot tables are all part of the same workbook and I can't understand why its saying can't find source data. Excel 2007 is being used on both computers.
View Replies!
View Related
Deleted Data Stays In Pivot Table..
I have been setting up some pivot tables. Down the left side of the pivot table is the values from a column labelled TRACKS. There are about eight different TRACKS. Say Track1, Track2, Track3, etc. I have set the data range up as a dynamic named range so that if more rows or columns are added to the data then the pivot table will automatically incorporate these. Today I wanted to test that the pivot was working as expected. One of the things I did was stick a few extra values into the TRACKS column on the data source. These were TEST1 and TEST2. These appeared in the TRACKS down the left side of the pivot table as expected. When I deleted these two test values, however, they would not disappear from the pivot table. There seemed to be nothing I could do to make them go away, except to rebuild the pivot table from scratch.
View Replies!
View Related
Pivot Table Cache :: Get Back Data
Let's say I have a pivot table made from data in another workbook. The other workbook is then deleted. Is there anyway to get the original data back? My particular file is about 10 MB, but if I "save as" it's only 1 MB. Plus, the pivot table "works," so I'm pretty sure Excel still has my data cached somewhere. How can I access it? I'm decent with VBA, so I'm open to any solution at all.
View Replies!
View Related
Automatically Include New Data In Pivot Table
I have a workbook that automatically creates a new sheet based on the current day. There is a pivot table in the workbook that gets values from multiple consolidation ranges. Right now I have to manually add the ranges from the new sheet every time. I need that pivot table to update with the same ranges from the new sheet every time a new sheet is created. There is also another sheet (named "Data Results") that needs to update too, but that's not as urgent. I've searched everywhere on the forums and can't find anything like that. When the file opens, I've written script to have a box pop up asking whether you want to "View Data" or "Begin New Audit." If you click "View Data" nothing happens and you can just view the file. If you click "Begin New Audit" it creates a new sheet based on the current date. I wanted to attach the file, but it's an Excel 2007 macro-enabled file (*.xlsm).
View Replies!
View Related
Pivot Chart And Read Data From Table
I have created some pivotcharts in Excel 2007, but I have a serious problem with them The labels (usernames actually) in the data table are impossible to read (pls see attachment) If I make the letters smaller it is unreadable, if I make them larger it is still the same. how I can make the data table "resized"?
View Replies!
View Related
Pivot Table For Large Amounts Of Data
if there was a way I can put it in to some kind of pivot table, because the hardest part for people when they read his list is it's so large it's hard to find data easily. So this is how he formatted his data... I was wondering what would be the best way to get this list in to a possible pivot table. This is a consilidated example, there are plenty more columns, but this will give you an idea of my problem. A2007 A2006 A2006 B2006 B2006 B2007 Feb Jan Feb Jan Feb Jan 630 Labor Cost 1000 7500 3000 4500 800 5000 624 Equipment Cost 900 50 40 300 20 1400 Now, the only thing I can think of is to make columns, but then I'd have to recopy all the task names (which there is about 700) for each of the different years (A= Actual, B= Budget, F= Forecast). Is there any other way that you can think of to do this with out making it so complicated? Any help or suggestions would be great. I really want some format that allows you to click the total and it goes to what makes up that total.
View Replies!
View Related
Pivot Table Field Not In Data Source
I am using pivot tables which were created by another (no longer contactable) and have found two fields in the field list which are not part of the source data i.e. neither are column headings but are used in the pivot table. My questions are: 1. How are these field created? 2. How can I examine these fields for formulae (as I have found one of them returns an incorrect value)? Field1 return a numerical value which is the difference between two columns Field2 shows this as a % (but incorrectly)
View Replies!
View Related
Pivot Table With Multiple Data Ranges
I have a workbook that contains 52 spreadsheets (one for each week of the year). Each contain the same column headings. The columns contain both numberic and text data which I need to pivot. I can individually pivot each sheet to obtain weekly data but I would like to obtain year to date data throughout the year. How can I merge all of my 52 sheets together to utilize just one pivot table.
View Replies!
View Related
Reformat Data For Pivot Table Source
My fixed asset software will not allow me to show multiple individual months of acquisitions. I can export each month. This will create a separate spreadsheet for each month, but the problem is that each spreadsheet may contain the same asset, but additions to that asset. Spreadsheet 1 ....Description............................Dept...................January acquisition .....Building____________________Plant______________1,000,000 Spreadsheet 2 .....Description..........................Dept...................February acquisition ......Building___________________Plant______________1,000,000 .....Welder____________________Metal_________________5,000 What I want is this Spreadsheet 3 .....Description.........................Dept..................January Acq..........February Acq ......Building__________________Plant____________1,000,000__________1,000,000 ......Welder___________________Metal_________________________________5,000
View Replies!
View Related
Validate Pivot Table Drill Down Data
I have a question that is bugging for quite some time, see if you know. Is it possible to insert a data validation drop-down menu to a pivot tables drill down data automatically? I really do believe that pivot tables should have this option, it would make data validation much more easier. I going to suggest that to the guys developing Office 14, according to them the new Office will more Business roled based. Lets wait and see a !
View Replies!
View Related
Text In Data Field Of Pivot Table
I am struggling with data field in pivot table. Is it not possible to use strings in data field? Here is a problem :- Col1-----------Col2-----------Col3 ---------------------------------- A 11/1/2007 Y A 11/2/2007 Y B 11/1/2007 Y B 11/2/2007 N C 11/1/2007 N C 11/2/2007 Y D 11/1/2007 N D 11/2/2007 N Required op :- 11/1/2007 11/2/2007 A Y Y B Y N C N Y D N N I used pivot table to generate this output but in a data field I don't get strings; instead it shows 1 in place of Y/N.
View Replies!
View Related
Data Field Of Pivot Table To Go Across Columns
When i tried to drop another field into data field, by default, the new data field will appear below the field which was already in there. However, i would like the new field showing in an new column - horizontally - rather than showing in a new row. For example: Rather than the Pivot table appear like this: Q108A 72 ____B84 ____C68 ____D5 Q109A 64 ____ B 73 ____C58 ____D 5 I would like the privot table present like the following format: ____ A_ B_ C_ D_ Q108 72 84 68 5 Q109 64 73 58 5 Is there setting I can change, so that the new field is added to a new column.
View Replies!
View Related
Sorting Data From Olap Pivot Table
i have generated a olap cube and display it using pivot table. But i am unable to sort my data from acsending order. For example: I had right clicked on my pivot table > field settings > Advanced > Select "Ascending". The data (sum of quantity) remains unsort after i have checked ok.
View Replies!
View Related
Format Data For Pivot Table Report
I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want. Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel. Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.
View Replies!
View Related
Pivot Table: Data Spread Across Different Worksheets
I have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?
View Replies!
View Related
Fill In Empty Data In Pivot Table
I am using a Pivot Table to sort, combine, and sum data. After this is complete I want to copy the pivot table and paste it (values only) to another worksheet and use the data again. The problem is the pivot table does not fill in all the blanks and leaves empty rows. The there a way to force the pivot table to fill in the labels?
View Replies!
View Related
|
TRACKER
