Excel 2007 :: Sheet Name In Show Details For Multi-sheet Pivot Table?
Jan 30, 2014
Excel 2007.
I have a workbook with a sheet per day, each sheet has a variety of 'jobs' as columns and a variety of people and kit as rows. Hours are manually input each day during the month.
Simplified version... These are two separate sheets 'M060114' and 'Tu070114'.
Monday
Dig
Drive
Dave
4
5
Bill
2
7
Tuesday
Dig
Drive
Dave
2
7
Bill
8
1
I have a Pivot Table on a separate sheet. This was created using the Wizard (ALT +D +P).
Dig
Drive
Dave
6
12
Bill
10
8
So far, lovely.
But when I double-click on a number or right-click and choose 'Show Details' I don't get the sheet names in the newly created information sheet. I get the Row label, column label and a list of the entries.
So if I clicked on 10 above...
Row
Column
Value
Bill
Dig
2
Bill
Dig
8
That's fine with just a couple of entries but with about 80 columns and 250 rows spread across a month, therefore 30ish sheets, it's difficult marrying up the 'Show Details' sheet to the actual data.
So after all that, the question. Is there a way of getting the sheet name to appear in the 'Show Details' information sheet?
View 1 Replies
ADVERTISEMENT
May 11, 2012
I have pieced together a VBA script to create a pivot table. Which is all great and works fine BUT for some reason it is now forcing the table onto a new worksheet. see code below, why it might be creating a new sheet. Alsothe sheet Worksheets("TICKETS BY OWNER & DAY") does exist.
Code:
' HERE WE CREATE THE PIVOT TABLES AND CHARTS FOR REPORTS
Dim WSD1 As Worksheet
Dim WSD2 As Worksheet
Dim WSD3 As Worksheet
[Code].....
View 1 Replies
View Related
Jun 26, 2013
I'm Using Excel 2007 and would like to have some VBA to work with the following!
I have a simple pivot table (PivotTable1) in Sheet1 with three items in the Report Filter which has been named "ROUTE"
I have created a ComboBox in Sheet2 and have added the identical three items in via format control, cell link A1.
I would like to be able to use the combobox in sheet2 to operate the PivotTable Report Filter in Sheet1 as I would like to build a report whereas a user. Can only select the comboBox and does not see the pivotTable
Sounds simple but cannot get this to work no matter what I try.
View 5 Replies
View Related
Feb 26, 2009
The size of this pivot table depends on how many different customers are in the data tab.
What I want is a macro that will show the details as if you were to double click on the grandtotal, but the grandtotal in column c is never in the same row and I am having difficulty coming up with a macro to do this.
There are some other functions it is performing as well so I just need to add this to the existing macro code.
View 6 Replies
View Related
May 4, 2011
I have a pivottable that has a calculated field returning a percentage of two other feilds. I have pivotcharted the result and now want to include a median of the calculated feild results on the same chart.
I want to use a pivot chart as it'll accomodate changes to the data range and different page fields.
using xl2007
View 3 Replies
View Related
Jul 6, 2007
I have a work sheet(sheet1) that contains 700 people's information. Each row contains information for one person. Let's call the first person A. I have to copy certain columns of person A's response to another work sheet(sheet3) in the same file. Then, copy person A's e-mail address and save the sheet as person A's e-mail. Repeat the same steps for the next respondent.
I have created the macro for person A already (including the saving part). Now the question is how to write the loop in the macro.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/5/2007 by Jean Kuo
'
'
Range("Q4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-4]"
Range("Q13").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-9]C[-14]"
Range("Q14").Select
Sheets("Sheet2").Select
Range("D4").Select
Sheets("Sheet3").Select
Range("Q14").Select
Sheets("Sheet2").Select
Range("D4").Select
ActiveCell.FormulaR1C1 = ""
Range("C4").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C17"
Range("C4").Select
Sheets("Sheet1").Select
Range("T1").Select
Sheets("Sheet3").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-10]C[-13]"
Range("Q15").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-11]C[-12]"
Range("Q16").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-12]C[-11]"
Range("Q17").Select
Sheets("Sheet2").Select
Range("F4").Select
Sheets("Sheet2").Select
Range("G5").Select
Sheets("Sheet3").Select
Range("Q17").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-13]C[-10]"
Range("Q22").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[42]"
Range("Q22").Select
Selection.Copy ...
View 3 Replies
View Related
Mar 13, 2014
when I open my sheet on another computer, all cell value which contain formula show no value. just blank. formula is link to same workbook on different tab. when I save as again on my desktop, all value appears. excel 2007 is using.
View 3 Replies
View Related
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
Jan 21, 2012
I get a "Object variable not set" error when creating a Pivot Table from a cache. I want the destination to be in a different sheet in the workbook. I set the new worksheet as: Set WSD = Worksheets.Add and I reference WSD.Cells(2,FinalCol +2) in the destination field of the CreatePivotTable method. I use the code from the Excel 2010 VBA book I got last week.
View 9 Replies
View Related
May 26, 2014
I am trying to link data from a pivot table we have in excel to a separate excel sheet.
The pivot table contains an up to date list of our inventory, however, we want to take that data and put it in a cleaner looking excel that we can e-mail to customers and publish on our website. Going individually, through every product takes too much time to have an up to date stock every day. Its even difficult to manage if we want to update just once a week.
When I link the cells of the pivot table to the new excel sheet they are constantly changing values as people are constantly editing the filters of the pivot table to look up various things.
View 6 Replies
View Related
Sep 19, 2013
I have a big DB with several rows and columns.
Column B has several card numbers (e.i. 7987654345678)
Column C has a location name (e.i. madrid)
Column L has a date (the date is when the card was used)
What I want to do is identify the cards that appear more then twice on the same date and the same place.
Card Number
Location
Date
123
LOC
18/01/2013
[Code] .........
I would like through a macro or pivot to show me the results of the 2 last rows, because those are the only ones that are exactly the same.
I have excel 2007 running on Windows7
View 2 Replies
View Related
Feb 15, 2012
I have 2 pivot tables on the one tab, called "Other VAT".
When I double-click a cell within the first pivot, instead of opening a new tab with the data, I want the data to appear on the same tab in cell A40, which is a free cell underneath both the pivot tables.
If I then doubleclick a cell within the second pivot table, I want it to appear underneath the data from the drill down of the first pivot.
View 1 Replies
View Related
Oct 24, 2007
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex)
On the report I have values 123A and 1234A
On the data tab I have table that has.
Ref Tot Value Desc Value1 Value2 Value3
1 123A Widgets 1 2 3
2 123A Widgets 4 5 6
Ref Tot Value Desc Value1 Value2 Value3
1 123B Nuts 7 8 9
5 123B Nuts 1 3 5
Ref Tot Value Desc Value1 Value2 Value3
7 1234A Bolts 2 4 6
11 1234A Bolts 3 5 8
So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View 14 Replies
View Related
Feb 9, 2012
I want to do the following -
Whenever I enter a value say "X" in Sheet1, ColumnA I want the value to be autofilled into sheet 2, ColumnA if and only if value does not exist in sheet2, Column A.
How to achieve this in Excel 2007 ?
View 1 Replies
View Related
Feb 5, 2007
I would like to use some VB code to search a Pivot Table, find a specific value and show the details relating to the value. I used the following code I have obtained from another thread in the forum
Sub Expand_Pivot_Values()
Dim rngFind As Range
Dim rngPTData As Range, rngPTC1 As Range
Dim rngF As Range, rngP As Range
Dim strFind As String
Set rngFind = ActiveSheet.Range("J3:J6")
'set the pivot table data range - see help file for property description
Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange
'set start cell for find
Set rngPTC1 = rngPTData.Cells(1)
For Each rngF In rngFind.Cells
'set variable to find........................
This code works fine but only identifies the first time the value appears in the pivot table and shows detail relating to that value. I need add to the code to search the pivot table and find recurring values. For example if the value is "2" I would like to show detail on each "2" value there is in the pivot table.
View 7 Replies
View Related
Jun 1, 2012
I have a query on array formula
Am using this formula to get the details from master sheet to template sheet
=IFERROR(INDEX('P O Detail'!$D:$D, SMALL(IF('P O Template'!$C$4='P O Detail'!$B:$B, ROW('P O Detail'!$A:$A)-ROW('P O Detail'!$A$2)+2), ROW(1:1))),"")
with Ctrl+Shift+Enter
This is working fine based on the input cell $C$4
What am looking for is ... how to add two more input data $D$4 & $E$4 so that based on these three values I should get the required output.
View 3 Replies
View Related
Apr 19, 2013
I prepared a Pivot Table and then i add some data in it but now all data is not showed by Pivot Table.
I do all necessary things like refresh and all but unable to increase a range.
How to increase range for Pivot Table in 2007.
View 3 Replies
View Related
Oct 9, 2008
I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?
View 9 Replies
View Related
Sep 26, 2011
I cannot remember how/if I can ALWAYS exclude blank results from my pivot table.
I'm hoping i don't need to use a macro, it should be in the settings for the pivot table, I hope...
I use Excel2007.
View 2 Replies
View Related
Apr 12, 2012
In my data, I have:
1. Age (up to 20, 21-30, 31-40)
2. Gender (male, female)
3. Location (London, Paris, Berlin)
I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:
Upto20 / 21-30 / 31-40 / Male / Female
I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..
Is there a way to specify this in a pivot table?
View 3 Replies
View Related
Dec 10, 2009
In Excel 2003 I created a macro that copied data from a table, pasted into a new sheet, created a pivot table, copied and pasted that data next to pivot table, sorted, copied and pasted into chart data. I've used this macro in Excel 2007 with no problems.
Now I'm trying to create a similar macro but it won't complete the pivot table. My Pivot Table Field List should have the Row Label and Values populated but when I run it all that is populated is the Values field.
Is there something in 2007 that will not allow me to create a macro using a Pivot Table or am I doing something wrong? The code is below if that helps ...
View 9 Replies
View Related
Feb 8, 2011
I have a pivot table that summarizes jobs that need to be completed on a specified date. I need to put that information on a calendar, but I would like to concatenate all information from that date so I can do a vlookup from the calendar. My other problem is that the number of cells change for each date, so I cannot specify exact cells, (some only have 1 entry, but others may have 4 or more). Can I specify a range of cells based on the result of the pivot table?
I have attached the pivot table, calendar and the data sheet with the vlookup info. Am I using the proper calendar or method to find this info?I have struggled with this for over a week now.
View 4 Replies
View Related
Jan 3, 2012
I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?
View 4 Replies
View Related
Mar 19, 2009
I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
View 9 Replies
View Related
Nov 9, 2011
I'm using Excel 2007. My pivot table seems to be limiting me to 256 columns in the Values/Data area. In researching below I believe that I should be able to have 16,000 columns in my Pivot Table.
[URL] The "Big Grid" and Increased Limits in Excel 2007
PivotTables Maximum rows displayed in a PivotTable report is 1 million.
Maximum columns displayed in a PivotTable report is 16,000.
Maximum number of unique items within a single Pivot field is 1 million.
Maximum number of fields visible in the Fields list is 16,000.
View 7 Replies
View Related
Jan 26, 2012
I have a table of data which I am analysing in a Pivot Table. For the majority of the data, the Pivot works very well: however I have a small issue, but it subsequently means the Pivot is useless.
Within the table array that I am referring to, there is a column of data of "Days per employee for a given period". The rows of data within the table array relate to every absence entry per employee, but this final column of data always contains the same figure (although can differ from employee to employee).
When I put the data into the Pivot, I can summarise the absence(s) as a simple sum. However, this final column of data should not be summed, since it is already the sum figure.
This figure though needs to be part of the Pivot, since I need to report on the percentage of absence days per type over the given period. So, the simple representation of =absence day(s)/worked days does not work... well I cannot get it to work. Additionally I have tried features like % of, but nothing.
I am using excel 2007.
View 5 Replies
View Related
Aug 8, 2012
I have a stripped down data source for debugging purposes. I only have 4 rows of data for test purposes. The dates are formatted as dates.
When I create the pivot table the dates become my column values. When I select the first date in the pivot table the Group By Field menu option is grayed out. I tried setting a tabular format but didn't work.
View 9 Replies
View Related
May 31, 2013
In Excel 2007 is it possible to filter the "Values" in a Pivot Table?
I am tracking the Gross weight of shipping containers. My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight.
for example:
Row Labels Sum of Gross Wt. - Lbs
10003150588929
10003153258700
10003155984958
1000315651530
10003156549761
I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible.
View 3 Replies
View Related
Sep 10, 2013
Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.
My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.
View 2 Replies
View Related
Aug 19, 2013
I am using Excel 2007 and my version of visual basic is 6.5.
I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.
------------------------------------------------------
Sub SwitchBoards()
Dim BoardNew As String
Sheets("Board Parameters").Select
' Make sure we get the right value.
[Code].....
The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!!
View 12 Replies
View Related
