Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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,


Similar Excel Video Tutorials

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


Usually when I want to create a pivot table. I choose the field I want and drag it to the specific place on the pivot 'format".

Now I am using 2007, and There is only a graphic that says...

Pivot Table 3
To build a report, choose fields from the PivtoTable Field List.

It is only a graphic and I cannot drag anything into it.. I must check the field I want from the field list and then drag it to one of the 4 boxes ( report, column, row, Values )..

Am I doing something wrong?
I create pivot tables in other 2007 excel sheets that operate the old way, but now this it wierd.

Thanks.


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 consist of all our orders information. (This is a direct lookup to a SP on a sqlserver so there is no datasheet) One of the fields is required ship date. I would like to color any value older than TODAY(). My problem is that this Pivot table is used by a lot of different people and the required ship date may move around on the pivot table so I have not been able to use Conditional formatting because the location of the field will change depending on the user. Is there away to tie this to the field? This Pivot table will grow and shrink by as much as 500 rows a day.

Thanks


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!


Hi,
I have setup a report with a pivot table based on different fields.
Due to low population of certain report selection, the pivot table field are sometimes missing and the result of it in my report is #REF!
(I am getting it with GETPIVOTDATA("Field1",$A$12,"Field2","Criteria2"))
How to set it to 0 in this case??

Thanks a million for support

Geoffroy
#REF!
#REF!



Hi,
I have been asked to maintain an excel97 worksheet, it contains two pivot tables in one workbook (Not my design).

In order to get the report I want I have to select a name from the "name selection" field on the 1st pivot (Left side) and I then have to select the "name selection"field on 2nd pivot table(right side).

This is complicated as the two name fields do not contain the data in the same order! Therefore I have to search the second selection field to find the same value as in the first selection field.

If this was a one off it might not be too bad, but I have to carry out the same operation seven to generate 1 report. I was wondering is there a way I can auto populate the two selection fields in the pivot table to generate the reports.


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



My boss sent me a workbook with a pivot table. This pivot table has two columns that are not on the worksheet the pivot table is built from and have formulas in them. I think they are part of the pivot table and not some kind of inserted columns because they are in the middle of other columns and when you select the drop down to add or subtract columns they appear in the drop down list. My problem is when I change the source data and refresh the pivot table the two columns move to the front of the columns and then the formulas don't work. Please help


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


Could somebody help me please.

I have a problem with a pivot table.

When I have completed and refreshed the pivot table, if I go into the main table of information and change the data or text field it then removes a field from the pivot table layout.

I have a report which contains 90 pivot tables which means when this occurs I need to go back and update each one. Which is very painful.

It seems to be just one particular field.??

If you can help I would be most grateful!!!


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.


Can anyone tell me if there's a limit to the number of rows you can use in a data table for a pivot table? My dataset has 50 columns x 10986 rows. Is this too large?

The message I get when I try to pivot is

"The pivot table name is not valid. To create a Pivot Table report, you must use data that is organised as a list with labelled columns. If you are changing the name of a Pivot Table field, you must type a new name for the field"

I'm a little confused as each of my columns are labelled