Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Unable To Drag Fields In Pt

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello All,

Im facing a very peculiar problem on my PT sheet on Excel '07. The Pivot table is based on an excel worksheet with about 800 odd data figures spread across 15 columns.

I've created the Pivot table and everything works fine, till I noticed that Im not able to drag fields or to be precise, pivot the table. Ofcourse I can do it from the Pivot Table Field List, but im not able to drag fields within the actual report. Infact im not able to get the single line cross icon when I move the cursor over the fields, instead of this I get the usual excel addition icon.

Im sure its a very simple problem and Im overlooking something, but not able to figure it out. Just to add to this, I do get the cross icon at the edge of the cells and when i try to move the field it throws up and error message saying :

You cannot move a part of the pivot table report or insert worksheet cells, rows or columns inside a Pivot Table report. To insert worksheet cell, rows or columns first move the Pivot Table report (with the Pivot Table Report selected, on the options tab, in the Actions group, click Move Pivot Table).To add, move or remove cells within the report do one of the following:

. Use the pivot table field list to add, move o remove fileds.
. Hide or group items in the row or column field.
. Modify the Source data.

Any sort of help with the problem is appreciated and wopuld request for an early assitance too, becasue Im already running late on schedule to submit the report. Just to add to this, the worksheet is not protected, just an additional information

Thanks and regards,

View Answers     

Similar Excel Tutorials

Quickly Resize Multiple Columns or Rows at Once in Excel
How to quickly resize multiple columns and rows at once in Excel.  This avoids having to individually resize rows ...
Input Form to Get Data and Store it in Another Tab in Excel
How to make a user input form in Excel and have the data stored on another worksheet at the click of a button. This ...
Thermometer Chart in Excel
How to create a thermometer chart in Excel. This is what we want: Steps to Create a Thermometer Chart in Excel Mak ...
Use a Form to Enter Data into a Table in Excel
You can enter data into a table in Excel using a form; here I'll show you how to do that. This is a great feature ...

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o

Similar Topics







Hi,
I have a pivot table with a report field. I need to create a second pivot table based on the results of the first one. Meaning, I would like that the report filter of the second pivot table will be a list of the names that got filtered by the first pivot table. Meaning, the row fields in the first pivot table will become the report field of the second pivot table.
Can it be done?
See file here
Tamir


Hi,
I am using Excel 2007. and I am new to the concept of pivot table.
I need to create the pivot table and chart with the data (from DB connectivity in it).
After getting the data in the sheet, I selected
Insert-> pivot table followed by the table range and location.
Pivot table report got created , but the pivot table field list is missing .And I searched to get that.Only after getting that, I can complete , by selecting the check box provided in it.So please help me on this issue.
Whether the "pivot table filedlist" will be available in some menu which is not present in my sheet.

Thanks,
Sowmya


I have a report that is generated into a pivot table. From that pivot table I am wanting to create another pivot table off of it. What I am wanting to have happen is that when the new initial report is updated daily, that I can link that to the pivot table report that I created so that it will update with only the fields I need. I know that I can copy and paste the report and doctor up the pivot table, but I am trying to make this more automated so I only have to set up the report I want once and then be able to link it to the initial report that is updated automatically. Any thoughts on how I can go about doing this?


I am sure this is a simple flip of the switch but here goes. I have a pivot table that every time I click on a field (Any field). The pivot table will begin to calculate and I get at the bottom Reading fields, updating fields, Calculating pivot table report with the progress bar. How can I turn that option off


Hello All,
I have searched the boards and have not been able to locate exactlly what I need. I am hoping that once again I can learn for the best. I apoligize up front if this topic has already been covered. I have 3 pivot tables in the same worksheet. Each pivot table pulls from the same pool of data and is layed out exactly the same. Each pivot table keys off of different row lables. I am utilizing 2 report filter options. What I am trying to accomplish is for 2 of the pivot tables to update to the same report filter options that I enter in the first pivot table. I have forud code that works with all pivot table in all worksheets with one report option. I just want the 3 pivot tables in this one sheet to work together. Any and all help will be greatly appreciated. THANKS!!


Hi Experts,

I have a pivot table that is being created of another excel table.

I have added new columns with formulae in this original table.

Now I want to use these fields in my pivot table. Can the pivot table fields be updated? Or will I have to create another pivot table to get the new fields.

Thanks in advance.


Hi all, I'm having problems showing the Pivot table as a subform and on a report.

The pivot table is fine when it's just on a form (as a pivot table view)

But when I try to drag it onto another form as a subform, it only shows it as a normal table but not as a pivot table (with all the calculations etc)

The same problem with trying to show it on a report...

Has anyone got any tips on how I could use the pivot table on different forms and reports and still allow it to show as a pivot table?

Many thanks!!
Mint


Hi,

I have the problem of showing data values on a MSchart (in excel userform). So I tried to insert the code below.
Dim i%

With MSChart1.Plot.SeriesCollection
For i = 1 to .Count
With .Item(1).DataPoints(-1).DataPointLabel
.Component = VtChLabelComponentValue
.LocationType = VtChLabelLocationTypeAbovePoint
.ValueFormat = "0.0"
End With
Next
End with


However I face this runtime error.

"You cannot change , move a part of , or insert cells in a pivot table report. If you want to insert cells on the worksheet , drag the entire report out of the way.

To move or add date to a report , do one of the following:

Use the pivottable and pivotchat wizard

Hide or group items in a row or column field

Modify the source data"

I couldn't figure out the reason of this error message. How can ı get over this error? Anybody have any idea?

Thanks in advance


I have been reading up on Pivot Tables and found in a book that I can add a calculated field by "Move the cell pointer anywhere withing the pivot table"; "Right-click and choose Formulas... Calculated Field from the shortcut menu...etc."

I have tried this numerous times, but when I right-click anywhere in the pivot table I get the following options: Format cells, pivot chart, Wizard, Refresh data, Hide, Select, Group & Show detail, Order, Field Settings, Table Options, Hide pivot table toolbar and Hide Field List. There is no option for "Formulas" How do I get this option?

Thanks


I have som raw report tables that holds a report date, which is the same for all rows in the table.

When managers print the pivot table based on these tables they need to see the reportdate, so they know how old the figures are (last updated).

Question 1: Is it possible to display this field even if it is not displayed in the pivot table?

Otherwise I will have it as a page field, but that is not user friendly, because they need to select the date. If the page field with the report date is selected (and therefore visible on prints), then it will make the pivot table empty with next refresh, because this date is changed to a newer report date.

As a compromise I have created an extra pivot table only taking max report date from the same pivot table source and displayed at the right of the page fields in the original page table (D2:E3).

I had hoped that GETPIVOTDATA function could do the trick, but I cannot find a way. I prefer not to create a user function with VBA, because Excel security might disable the function when the user opens the spreadsheet.

Johan


I have a pivot table that pulls in data from another sheet within the workbook. Depending on what report filter is selected in the report filter field within the pivot table, the resulting table varies in size, e.g. it can occupy cells A4:D10 or A4:D50 depending on the filter selected. Below the pivot table is further data, which is separate to the pivot table data but needs to be displayed on the same page.

The data below the pivot starts at row 51, as this is the first row that is definitely free when the pivot table is its maximum size, ie occupying cells A4:D50. However, depending on the report filter in operation, the pivot table is not always that big, so if the pivot table occupies cells A4:D10 for example, rows 11-50 are completely empty. I would like to be able to automatically hide these rows if there is no data in them, ie if the pivot table filter returns a smaller data set, I want the unused rows below it to be hidden in that view. In the example above, this would mean rows 11-50 would be hidden.

Someone suggested previously that I use this method but the problem here is that when I use this code it actually hides all the rows in the specified range, regardless of whether they are empty or not. I think this is somehow related to the fact the code looks for the sum of the values in the rows, and although there are numbers in my pivot table, perhaps it treats numbers in a pivot table differently? Certainly, the code works when I create a simple test file with numbers in it (and no pivot table), but does not work in the case of my pivot table, even though there are numbers displayed within the range specified.

Can anyone advise on how to do this? Thanks!


Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter


I have a pivot table that pulls in data from another sheet within the workbook. Depending on what report filter is selected in the report filter field within the pivot table, the resulting table varies in size, e.g. it can occupy cells A4:D10 or A4:D50 depending on the filter selected. Below the pivot table is further data, which is separate to the pivot table data but needs to be displayed on the same page.

The further data below the pivot starts at row 51, as this is the first row that is definitely free when the pivot table is its maximum size, ie occupying cells A4:D50. However, depending on the report filter in operation, the pivot table is not always that big, so if the pivot table occupies cells A4:D10 for example, rows 11-50 are completely empty. I would like to be able to automatically hide these rows if there is no data in them, ie if the pivot table filter returns a smaller data set, I want the unused rows below it to be hidden in that view. In the example above, this would mean rows 11-50 would be hidden.

So what I want would be something like this:
If cells A4:D50 = ""
Then Hide.EntireRow

If cells A4:D50 = "has any value"
Then Show.EntireRow

Can anyone advise on how to do this? Thanks!


I currently have 2 pivot tables on 1 sheet directly above each other, with
all the total hidden so that it looks continuos. The reason I am using 2
pivot tables is as the data comes from different sheets.

The problem I have got is when I refresh the top pivot table as the contents
of the sheet it is pulling from is populated with more data it says that 'A
Pivot Table report cannont overlap another PivotTable report'

Is there anyway that I can prevent this from happening?

Can you set the format like on a chart where the pivot table will move if
more cells are inserted.

I hope that I have explained this well enough!

The Rook



I have a Pivot Table based on data that gets imported.

The number of rows in the Pivot Data varies depending on the underying
data.

Below the Pivot Table are some calculations and then a couple more
Pivot Tables.

I'm building a Profit & Loss report and each Pivot table summarises the
relevant report sections (e.g. Sales Pivot Table, Cost of Sales Pivot
Table).

The problem is, when the pivot table refreshes, it can overwrite
formulas and tables below it if the table increases in rows.

Any ideas/suggestions please to prevent this.

Thanks

T Hunt




Hi,
I am using Excel 2007. and I am new to the concept of pivot table.
I need to create the pivot table and chart with the data (from DB connectivity in it).
After getting the data in the sheet, I selected
Insert-> pivot table followed by the table range and location.
Pivot table report got created , but the pivot table field list is missing.And I searched to get that.Only after getting that, I can complete , by selecting the check box provided in it.So please help me on this issue.
Whether the "pivot table filedlist" will be available in some menu, or should come automatically....it's not at all coming in my sheet.

Thanks,
Sowmya


Hi I have 3 pivot tables in same sheet and data source is danamically changing when refresh depend on the parameter value which is passing from the front end. It is a report and I have done the layout. But sometimes when the row count is more, Im getting above error message which is "A Pivot Table report cannot overlap another Pivot Table report" .

Do we have any setting to do setup to auto add cells depend on no of rows ? like in tables/data range ?

How can we resolve this issue dynamically ?

Thanks everyone in adavance.


I posted a similar message about Pivot Table Page Field Sorting about a week ago and the response I got worked like a charm.

I am now trying to do some grouping with a page field. Currently I have a Page Field in a Pivot Table with a list of food items. I'd like to be able group those items without having to move the Page Field down into the Rows or Columns section of the Pivot Table. I know how to programatically move the field into the body of the Pivot and then select the cells I want to create the group, and then subsequently move the field back up into the Page Field section. However, I would like to find a more elegant way to do this without having to move the Page Field at all. Can anybody help?

Here is an example of the items that I have in the Page Field called Food_Items:

Whopper
Big Mac
1/4 Pounder
French Fries
Tater Tots
Apple Slices

Basically what I would like to do is group the "Whopper, Big Mac, and 1/4 Pounder" into a category called "Hamburgers. This new field created with the grouped names would be called "Food_Items2" and be the list would look something like this:

Hamburgers
French Fries
Tater Tots
Apple Slices

Thanks for all your help.


I have a pivot table that calculates the number of hours of each type of time. By default excel puts these calculated field (based on fields that the pivot table pulls) as rows for the employee.

I am looking for a way to put these field as columns either directly from the pivot table or a way to convert the data from the pivot table. See screenshots attached. xls1 is what I have, xls2 is what i would like.


Thanks for anyone's help!


Hi,

I have been having a problem with a report that I have created. I have a pivot table reading off a raw data sheet and this pivot table has many calculated fields. The current pivot table source is: '6Contracts'!rawdatacols where 6Contracts is the name of the sheet and I have a defined name which includes all the columns with data on this sheet.
My problem is that every now and then I need to add a new column to my raw data sheet. When I do this some of the calculated fields change to #NAME? but not all of them, eg. =IF('#NAME?'=0,0,'#NAME?'/'#NAME?')
Some of my calculated fields rely on other calculated fields as some of these calculations are quite complex and I had to split it up to fit them in.
Any idea why this is happening and how I can solve this without having to recreate these calculated fileds each time I add a new column to my source data?

Thanks in advance...

Good evening.

I have a pivot table with roughly 9 columns/fields which can be filtered upon. I find that people filter on a field and then save the workbook, leaving it filtered for the next person and they think data is missing I'd like to be able to write a macro and assign it to a button so that it unfilters (ticks SHOW ALL) for each of the fields in the pivot table. Can anyone help with this? I have provided an image of the table. The pivot table is on sheet, "Report"




Dear All,

I have a report in Access 2007 that contains a few pivot tables on one page. The pivot tables all use the same table for their source data, Open Sales Orders. Each pivot table summaries dollars based on different fields: on supplier, product, customer, etc. All of these work fine and the report is fine.

My problem is I want to generate in the same report but include one page per salesperson. Each page will use the same pivot tables but filtered by each salesperson. I want to end up with one page that has the pivot tables summarizing all data, which I have now. Then one page summarizing data for each salesperson. Each salesperson page will have the same pivot tables.

I am quite familar with pivot tables in Excel. I see the pivot tables in Access also allow for a filter field. I added the Salesperson field as a filter field of all the pivot tables. I left the filter field selection as All.

I added a grouping to my report by salesperson. In that section, I copied all the pivot tables and the salesperson field so to identify the person.

The report generates the correct number of pages, each labeled with the salesperson name. However, the pivot tables are not being filtered on each salesperson. Every page has the same data.

Does anyone have any suggestions?

Do I have to create pivot tables filtered by each salesperson and add them individually to the report? At any time, some salespersons might not have open sales orders so there would be no data and I would want to skip pages for them. Also, when we add or subtract a salesperson, I would have to remember to change the report.

I am at best an intermediate user of Access. Therefore, please be detailed in your responses.

Thank you very much,

GL


I'm trying to change the data source of an existing pivot table, but I get the message "a pivot table report cannot overlap another pivot table report". This is the only pivot table on the worksheet. This is my code. The error occurs on the line shown in red.

With Sheets("Analytic Table")
.PivotTables("PT-Analysis").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Input Table'!R1C21:R" & NumRows & "C22"
.Visible = True
.Select
.PivotTables("PT-Analysis").PivotCache.Refresh
.Visible = False
End With

Can anyone tell me how to reference the existing pivot table and just update its data source? TIA.


In my pivot table, I want to hide and show different fields at different times.

When I uncheck the box for the field I want to hide, under "Choose fields to add to report:", it hides the field like I want it to.

When I check the box to show the field, that works, but my data becomes formatted as currency! Neither my the pivot table, or that column in the source data is formatted as currency -- it's just a general number with a thousands separator.


My pivot table has two row fields and one data field.
The far right row field is the pub name and the left row field is available inventory. My data is monthly copies shipped. I want to sort my data in descending order by inventory. When i do field settings - advanced - descending - using field available inventory nothing happens. If I remove the pub name from the pivot table then it sorts by inventory but I need the pub name in the pivot report. Thanks for any advice.