Is there a way to create a pivot from multiple external worksheets. I have the same format and field names in different folders on our network. I am proabably looking at reading from 5 different files. Is it an option to write a query with UNION ALL?
I upgraded to Excel 2010 from Excel 2000 a couple months ago. In Excel 2010, after double-clicking on values in a pivot table to create a new worksheets, I'm unable to highlight those worksheets and add a column to all of them. I was able to do this in Excel 2000... I can't figure out what's stopping it from happening for the life of me. Am I missing something obvious here?
I am using pivotable with access queery as the source.
When I set it I cuse that external source browse tool, but it seems to only recognize the source if the dive is the same. When I go downstairs to try on a users computer there dirive letter(K is different than my (H and it does not work.
I am using an Excel file (Raw.xlsx) to store my raw data, and take another Excel file (Pivot.xlsx) to make PivotTable from Raw.xlsx
When generating the PivotTable, every works fine. When click [Refresh] in the Raw.xlsx, whatever changes been made in the Raw.xlsx can "sync" to the Pivot.xlsx
However, once I save and reopen the Pivot.xlsx, I can't refresh the PivotTable anymore. I've tried changing Data Source, but it doesn't work.
I have a pivot table created from a cube of data not within the file I am working in. I have created a macro that will update, but only for the specified timeframe. I have a separate worksheet within my file that will allow a manger to specify what timeframe he wants to see. Can I edit a macro to read from a source that is different than where the data is being pulled from?
I have a pivot table report connected to an external data source (OLAP cube).I want to automatically refresh the pivot table report everytime the OLAP cube data changes.
I know we can use Automatic refresh on open of workbook or time intervals after which to refresh .But i want the auto refresh to work even if the workbook is already open and there is no definite time interval after which the cube is likely to change.SO,by defining intervals for auto refresh i do not intend to waste time refreshing even if there is no change.
There are multiple pivot table reports and pivot charts connected to the same OLAP cube.
I have two worksheets. One shows imports, the other sales.
I might import Item A at a price of $1.00 and a few months later Item A costs $1.25. I got 100 at $1.00 and 40 at $1.25.
I have sold a total of 110 items.
What I want to do is make a pivot table from both worksheets. I want it to show that I imported 140 units of Item A at a total cost of $150.00 yet I have only spent $112.50. My arrangement with the source vendor is to pay AFTER the product is sold. So I owe $112.50 to the source vendor.
I've tried copy and paste of one sheet into a 3rd new sheet and then copy and paste the common information PLUS Quantity Sold from the 2nd sheet into the 3rd.
When I make the pivot table, and I can not get Item A to multiply 100 x $1.00 and 10 x $1.25. It shows sales of 100 and sales of 10. I can get it to show costs of $1.00 and $1.25. But I can not get it to multiply, as the figures are one two different lines.
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
I want to create a summary pivot table report + chart. What I have is 12 worksheets, one for each month, each with total amount invoiced and gross profit by client.
I want a pivot table that shows me the clients and amounts each month (this is the bit stumping me). Basically I want a drop down box to select the month on the pivot table, which then shows the data from the relevant worksheet (Aug-09, Sep-09 or whatever).
I've tried using multiple consolidation ranges but this just aggregates all the data; it doesn't allow me to limit by the worksheet the data has come from.
I'm wondering if I have to consolidate all my months into one big sheet, then add a field for the month. I would rather avoid this but accept it might be the only way.
Final thing; can't use VBA at all (too much hassle for IT apparently).
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?
I believe in the past that I have told Excel to send the output of the pivot table to different worksheets. For example if I have three regions- the pivot table would send the output to three worksheets and name the worksheets with the three regions.
I am using Excel 2007. I have a population that I used to create a pivot table. I am currently double clicking on the value cells to create worksheets of only particular "row label" categories. I am then copying the "row label" information into the newly generated work sheet name tab. This works fine when I only have a few "row label" categories to do but it is tedious if there are many categories.
Is there any way to automate the creation of work sheets for all row label values and also naming each work sheet tab with it's respective row label information. Here are images of the pibot table and the type of work sheet I would like for wall row label values.
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
Background: I know ZERO about VBA and I am a Vlookup & Pivot Table noob, but experimenting everyday.
Every week, since August 29, 2009, I get a sales report from my customer in raw form showing point of sales data for the previous 7 days. After manipulating the data I end up with 11 columns.
Now, if I am only concerned with that one week then I can drop the whole sheet into a Pivot Table and all is well.
However, I have 12 sheets now (with more to come), one for each week, and the row count ranges from 3,328 to 16,414.
I do not want to consolidate the data.
I can copy everything to one sheet, and hope not to max out at the million row mark, but would prefer to keep it tabbed by week.
I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.
My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.
I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I have a file with 26 worksheets, each have four columns and the last column is averaged at the bottom, and I'd like to make a summary worksheet tab showing the name of the table, the number of records, and the average score of all 26 sheets.
The problem is, some worksheets have 20 rows, some have 1400 rows. I could go choose the data manually, but isn't there an easier way to do this?
The table name is merge/centered across the four columns, and is the worksheet name (using a formula I found on here). Then the four columns have headers, then data. The last row of each worksheet doesn't have the normal data in it, but says "Records Counted," then has a COUNT of the rows, then has "Average," then has an average of the column above.
I have a huge excel file. This file contains Projects, Project Manager, departments and sales for different quarters. Each department has sales and the quarter the sale was done. Now I want to create a pivot table where I can see the sales for each quarter for each project or each project manager. Attached file may elaborate the problem. ShaA1.xlsx
I have a list of people whose direct reports have either completed or not completed a task at work, and there are four tasks. The records look like this (I've removed the direct report's name, which is to the left):
I want to pivot the data so that for each manager I can see the % who completed Task 1, the % who completed Task 2, etc. preferrably in a horizontal format.
So, I can get this easy enough for the first task:
Count of Task 1Column LabelsRow LabelsCompleteIncompleteGroup 1Danny Ledbetter67%33%Mickey Smith75%25%
However to the right of this I want the results for Task 2, 3, and 4. The best I can do is the following:
Column LabelsCompleteIncompleteRow LabelsCount of Task 1Count of Task 2Count of Task 1Count of Task 2Danny Ledbetter67%67%33%33%Mickey Smith75%75%25%25%Grand Total71%71%29%29%
The results for Task 2 are incorrect. I've tried % of Row, % of Parent Row, and all the other calculations, and can't seem to get it. I've also tried putting the fields in the Column Labels box, the Values Box, etc.
I can even get this if I put the SumValues field in the Row Labels, which yields a vertical result, but still, the values are incorrect (Mickey has 100% completion under Task 2):
Column LabelsRow LabelsCompleteIncompleteDanny LedbetterCount of Task 167%33%Count of Task 267%33%Mickey SmithCount of Task 175%25%Count of Task 275%25%Total Count of Task 171%29%Total Count of Task 271%29%
How do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.
I am looking a macro that will create as many graphs as there are counties (column a) in the data range (A4:N89 for this example). There could be a maximum of ~3000 counties, which would place the data range at roughly excel's limit (A4:N65000).
In any case, the graphs would take data from column F and look like the 3 graphs i have provided in the attachment. The graphs are simply graphing share for the top 25 lenders in each county. So, for example, Texas contains 254 counties--I would like a macro that creates 254 graphs, one next to the other, starting at cell O4 through column IV4 and then repeating at O22 through column IV22 and so on, until all the graphs for each county in the data range are created.
Also, would it be possible, just as in the attachment, for the macro to change the color of the data bar associated with a particular lender, in this case JPMORGAN CHASE BANK? In the attachment, I made the three bars associated with JPMORGAN CHASE red. If there is a county in which this particular is not in the Top 25, then all of the bar colors in the graph remain blue.
Would it be possible to do the exact same (macro that graphs off the pivot table) but include the data for the Top 25 Totals, Non-Top 25 Totals, and the County Total?
In other words, the graphs would look exactly as they do now, but, for example, the first graph would contain data from F5:F32 rather than just from F5:F29. Furthermore, the graph's axis label would pull from C5:C29 AND B30:B31 AND A32.
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
I am trying to create a single pivot table using data from multiple sheets (one per month). I am trying to summarise the headcounts (count of assignment number) by month in a single table and then be able to select single months/areas or combination of months/areas in the same way as a single sheet pivot table. I have managed to get the table part way there but it’s not counting the assignment number and my Jan data is not appearing (see attached). How do I fix this? (I have only used 5 months of year in example due to the large file size) Alternatively is there a different or better way to this other than pivot table?
In the attached file (xlsx) under 'Database' Tab poeple have indicated their preferences (multiple choices) for different food items "specialties". The specialties are grouped under broader buckets called "groups". The specialties are bucketed into groups in a way that people end up more than once in each specialties and groups due to their muliple selections. When we create a pivot by specialties (Pivot Specialties tab), each person appears only once for each specialty...it's great. But, when we create a "group" pivot (each group has multiple specialties), now, people appear more than once for each group. Is there a way, each person can show up only once under each group so the group count does not appear to be misleading?
I am currently trying to create a multiple pivot tables in one sheet. The current formula to make a single pivot table that i am using is
Formula:
[Code]....
The current problem i have is... i need to Dim PT01 as pivottable, Dim PT02 as pivottable, Dim PT03 as pivottable and so on.... Repetition is not good!
and secondly,the total pivot table is not constant each week. During busy week, i need to create 8 pivot table and in a slow week, it can go down to 5.
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
I'm trying to write a macro to select the multiple sets of the same data for several PIVOT tables. I've tried Slicers but it seems that this takes up too much processing power and always times out.
My workaround is to do a macro that picks out the said data, however when i do the below, plus another 4-500 lines i get told that there are too many line continuations