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 6 RepliesI have 3 tabs in my spreadsheet that I want to combine in a Pivot Table. All 3 tabs have the exact same headings in Columns A-J. I want to create a pivot table to pull in all the data from the 3 tabs while using the Current Column headings to create the pivot table. But the pivot table wizard is only giving me "ROW" and "COLUMN" which does not allow any flexibility to create the table the way I want and move the various column data around.
Is there a better way to Pivot Table data from the separate tabs? My data on each tab is changed weekly and I was hoping to just update the pivot table when the data changed..
I have looked and experimented on how to include multiple ranges on a pivot table in excel 2011, i have found how to to it with the wizard but i can not find the wizard.
How to separate the ranges in the selection box? They are going to be going across sheet is that makes any difference.
I would like to use a pivot table to manipulate my data. However, I need to be able to select data within my data set by a range of dates (usually a week). Is there a way to use a pivot table but reduce the data set by a user enter range of dates?
The date field is the first column in my data sheet.
My pivot table wants to total 2 sets of data and put it in rows. I would like it to be in columns.
The small attached sample shows what I would like.
I have just created a pivot table, however, i have just put the range to the cells that have data in them. The problem is, is that i want to make the cell range bigger, but the cells that i want to include do not have data YET, but will in the future. I have tried creating the pivot tables including the cells with no values, but it ends up puting 1 into the pivot tables cells for some reason.
View 9 Replies View RelatedOn a worksheet, I created:
- 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 sheet that is a download off of another system every month which means that the number of rows change from month to month. I have a pivot table of this data as well but I want it to change it's data range as it changes month to month. I have a Formula in cell C1 that defines the range, the formula is ="A5"&":"&"S"&B1 {B1 being the =COUNTA(A:A)}
Which yields A5:S31. Logically the way I thought this would work is by setting the Pivot Table range to : =Indirect(com.jdedwards.jas!$C$1) But this is not allowed as the range in a pivot table must be more than two rows. The second thing I tried was naming the range. I went to insert->Name->define and set the name to "Download" and the range to =Indirect(com.jdedwards.jas!$C$1). it didn't give me any errors but now I don't know how to set the pivot table to that named range and i don't even know if that is really going to work.
I need to read consantly changing shift time/ covered data from a Pivot Tables pivot chart and populate this data into number of shifts covered/ uncovered. This information is then put into a chart over a 24 hour period (from 0700 to 0700). I have been populating the data from the pivot chart by hand by referencing the number of shifts in the covered line and dragging it to correspond to the shift time data part. I then have to do this for the uncovered shifts. As the data in the pivot chart is constantly changing, i need to do this data ransfer 'automatically'. I have started to look at and learn VBA, but i am getting nowhere fast. I enclose a worksheet (blank) to give you an idea fo what i am trying to do.
View 4 Replies View Relatedfind the data Attached. I have 3 work books which contain logged, issued and cancelled data stored in different work book. There are two common fields like Location & segment in all the work books whcih are common. I want the data in a sequence by which consolidation should happen for location and the segemnt should be a page field and datas are arranged accordingly. I am attaching a work book.
how to do this in multiple consolidation of ranges in pivot or any other way.
I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.
Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...
Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg
I have a range of data that is added to constently. I named the range "Download" and defined it as =Indirect(Sheet1!B3) where Sheet1!B3 has another formula which produces the range of the data. I want to apply the named range to a pivot table so that when it updates, the range is updated as well but i'm not sure on how to do this. I put the word "download" in the range input of the pivot table wizard but it results in an error and doesn't complete. how can i set the pivot table's range to the named range that I defined?
View 5 Replies View RelatedMy boss asked me to format a pivot table like this with irregular ranges:
I've had a crack (as seen below and have gotten everything right except the strange group irregular ranges. I'm stumped! I'm not sure if pivot tables even have a function for irregular group ranges.
My raw data looks like here is its layout:
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies View RelatedI 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.
Cells in row C have numbers (number of days between date a and date b)
I want to group the days so I can run a usable pivot table as follows:
Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +
My attempt is as follows but only give two results and not 4?
=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedI 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 ?
I manage a team that quote for business. They are targetted on number of quotes per month but then also wins per month, however a win may come a number of months after the original quote was generated.
They work off a spreadsheet where they log:
Quote date (And month)
Quote Value
... customer info etc
Win month (against orginal quote information)
Win value (against orginal quote information)
I started writing my pivot using the Quote month as the main reference point, IE Quote Month in the Row, and then put all the data in the central drop data section... However, there are two immediate problems with this:
They are targetted on quotes generated per month, some business won has come from last year, which they are lifting from the old spreadsheet and puttin in my new one... meaning that quotes they generated in Oct 2012 are now showing up as quotes for October this year.
If the wins span a number of months they are showing in January (if quoted in January) and not in March (when actually won)
Is there a better way of writing my pivot table??? There must be... ideally what I would like is half the table referencing the Quote date and half referencing the order date... but I don't know how to do this? I could probably do this with pages but I would like to show all on one sheet...
I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?
View 2 Replies View Relatedi 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
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
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
View 9 Replies View RelatedI 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):
Manager NameTask 1Task 2Task 3Task 4Mickey SmithCompleteCompleteCompleteCompleteDanny Ledbetter
IncompleteIncompleteCompleteCompleteDanny LedbetterCompleteCompleteCompleteIncompleteMickey SmithCompleteCompleteCompleteIncompleteMickey SmithCompleteCompleteCompleteIncompleteMickey SmithIncompleteCompleteCompleteIncompleteDanny LedbetterCompleteCompleteCompleteComplete
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.
View 4 Replies View Relatedsee attachment.
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.
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?
View 1 Replies View RelatedI 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.
View 2 Replies View Related