Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Don't Show Blank Pivot Table Fields In Drop-downs


I am creating a pivot table from region, which some of the rows are blank.

Is there any possibility that the pivot table not show the blank in its dropdown combobox?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Don't Display Blank Entries In Drop Down Menu
If i make the listfillrange of my combobox a named range, is there a setting in the combobox that allows it to ignore any blank cells in the named range.

Lets say my named range is cells A1:A5 but only A1 has a value, the rest are blank.

The combobox drop down menu will display the value of cell A1 and then 4 blank rows. Can i make the combobox igoner the blank rows and only display cells A1 in the drop down menu?

View Replies!   View Related
Hide/Show Pivot Table Fields
Im using the following VBA code, but the on error resume does nothing, excel throws out the error 1004 "Unable to get the pivotfields property of the pivottable class" (i know why the error is caused and how to fix it but my error trapping wont work). Is there some option which i have turned off which prevents me from trapping errors?

Dim ws As Worksheet
Set ws = Excel.Sheets("Sheet1")
On Error Goto err:
If ws. Range("IV1").Value = "dog" Then
ws.PivotTables("PivotTable1").PivotFields("Price Euros").Orientation = xlHidden
Else
ws.PivotTables("PivotTable1").PivotFields("Price Dollars").Orientation = xlHidden
End If

err:
msgbox "invalid"

View Replies!   View Related
Pivot Table Total Rows Don't Match Data
I have run a pivot table state, city, address, floor and all the maths works out fine.. but the name in the 'total' rows, does not match the name in the data rows.. e.g. texas, dallas, 57 oilrig street has total rows saying CA toal,Newark total, 560 mission street total (obviously split across the appropriate rows for each total) but in each case, all those square feet, or dollars add up perfectly, for the data rows above them. double clicking the totals shows no sign of the rogue location... but these same entries are in EVERY set of totals in the pivot table!

POSSIBLE clue.. I generated the pivot at home, on Office 2003, and am now viewing it at work in 2002 version. (buit this could have nothing to do with it at all. Am I missing something obvious, or do i have good reason to pull my hair outr in frustration (first time this has happenned in a year of doing these reports daily)

View Replies!   View Related
Pivot Table Drill Downs To Appear In New Worksheet
When you double click on data in a pivot table this brings up the drill down info. Is it possible for this to appear in a new workbook rather than the active workbook?

Any help if grately appreciated. If you need more information please post what you require and i will reply.

View Replies!   View Related
Show Only Rows That Don't Contain Certain Word
Looking for a VB solution to parse each cell in column B if a specific word exists then hide the entire row. in this case the word or phrase will be (inactive) each row will be different words but can contain (

like this...
___A___B____________________________________________C
1 44 this row does not contain the word but has other 3
2 23 this row does not contain the word but has other 5
3 21 this row does contain the word (inactive).................Hide the Row
4 26 this row does contain the word (inactive).................Hide the Row
5 43 this row does not contain the word but has other 6
6 20 this row does not contain the word but has other 9

and so on....

View Replies!   View Related
Don't Copy Blank Cells
i have a small macro that is deign to move info from sheet1 to sheet2,

column B in sheet one has a list of names, not every cell has a name in it, if cell B8 has a name then that cell and cellC8 is copyied to sheet2 column B&C.
The macro i have copies over the blanks as well and i end up with sheet2 column B cell 8 filled cell 12 filled in etc.

How do i stop the blanks?

Private Sub CommandButton2_Click()
'Transfer Results

ActiveSheet.Range("B14:C400").copy
Worksheets("points").Select
Sheets("Points").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

End Sub

View Replies!   View Related
Count Pivot Fields In Pivot Table
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
ghost.Delete
Next ghost
Next Count
pt.ManualUpdate = False
End Sub

My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............

View Replies!   View Related
Don't Plot Blank Cells In Bar Chart
I am trying to set up a bar chart that will not plot values where I have left the cell blank so that I don't have to keep resizing the source data / sorting the data whenever I add more data.

I have tried putting NA() as the value as you would for scatter plots
I have also tried leaving the category cell blank

Neither of these seem to work. I have attached an example.

View Replies!   View Related
Delete Blank Rows :: That Don't Contain Visible Numbers Or Letters
I'm trying to clean up several long worksheets by removing rows that don't contain a visable number or letter.

I want to remove all rows that only contain either empty cells, formulas that evaluate to a empty text string (""), cells that contain error values, or cells that contain zeros, or any combination of the previous.

I've stumbled onto the following, but it does not work.

View Replies!   View Related
Formatting Pivot Table Fields
I'm trying to loop through all pivot fields in a table and change the format to be 0 decimals and comma seperated.

Here's my

View Replies!   View Related
Pivot Table - Hiding Fields
I have a question regarding pivot tables. how do I hide a field but still maintain that filter? I have a field in the Column Fields titled "International". Right now I've filtered out only the YES entries, but I don't want to see a "YES" column beside each. When I try to hide the column the filter is reset and all entries, international or not, are seen. Can I hide the column but maintain its filter?

View Replies!   View Related
Pivot Table And Counting Unique Fields
Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.

I have a very simple excel 2007 document that has one column with multiple rows;

blue
Blue
green
red
red
green
Green
blue
red
Blue
blue
Bleue
Rouge
red
Bleue

I would like the easiest way to count the total of each and sort in highest first,

e.g.

red 4
blue 3
Blue 2
green 2


View Replies!   View Related
Reset Pivot Table Fields In Multiple Sheets
I have pivot tables in diff pages
im trying to make a button to reset all the fields in all the pivot tables..

i tried recording a macro to do this but it only works for page fields..
it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....

this isthe code it gave:

View Replies!   View Related
Using References In Pivot Table Calculated Fields Formula
Worksheet A contains two columns, that maps individual operations to their unitary cost:
COLUMN 1 = a list of operations
COLUMN 2 = the cost associated with each operation.

I can do a vlookup on this worksheet to retrieve the cost of each individual operation.

On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.

operation 1 was performed 5 times
operation 2 was performed 7 times
operation 3 was performed 4 times

I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be
= operation * vlookup("name of operation","range for lookup table",2,0)

... unfortunately, "references, names and arrays are not supported in pivottable formulas".


View Replies!   View Related
Removing Data Fields With Vba In Pivot Table
I have written code to add datafiels to pivot table but I need to remove the ones that are their prior to adding.

Does anyone have a technique using vba to do this.

View Replies!   View Related
Pivot Table Fields, Based On Column References
I have a pivot table which draws data automatically from a database

What I would like it for the customer field of the pivot table to only equal the customers which are present in another worksheet (Column A:A)

View Replies!   View Related
Pivot Table Creates Extra Data In Fields
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.

View Replies!   View Related
Listing Pivot Table Field List Fields
Is there any code or way to generate the compete list of fields that are selectable from the " PivotTable Field list"?

I have various cubes I need to check and compile the list of fields for each.

View Replies!   View Related
Macro To Print Pivot Table Page Fields
I want to print out, for a range of 'Page Fields', from a Pivot table automatically.

The Macro must be able to:

Automatically go through and select the 'Page Fields' in the pivot table

Select the print area range automatically, which will change each time the pivot is refreshed/reselected.

Set the print area to fit the page for each category selected in the pivot table

Finally print.

Can't find code for this anywhere, but i must admit got fed up with looking

View Replies!   View Related
Test/Compare Pivot Table Fields Macro Code
I wrote a macro for a pivot table.

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Export!R1C1:R3000C53").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Reference"), "Count of Reference", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("ACN received date")
.Orientation = xlRowField
.Position = 1
End With

What i want to know is if there is a code line to test if "Non Processed" value exists in column "Status" and then hide the value.

Now, if the macro does not find one value just gives me error.

View Replies!   View Related
If Blank Cells, Paint Yellow. If Cell To Left Is Less Than A Number, Don't Paint
I have a spreadsheet which contains 39 columns with a header row. The number of rows varies per spreadsheet. I'm running into a problem with highlighting(painting yellow) cells. Really the problem is selecting the cells to be highlighted. The macro is skipping some of my code completely and in other cases leaving some cells unhighlighted.

1. All blank cells in row AG should be painted yellow.

2. If column AF contains a cell with number 900 or greater, then the cell in AG (same row) should NOT be painted yellow.

3. All blank cells in rows AC and G should be painted yellow. Some spreadsheets may not contain blank cells.

Problem with the VB is not performing the code after the first command. It will highlight MOST of the cells in AG but for some reason it is leaving a few blank ones unhighlighted. The blank ones are usually at the bottom of the rows. I have no idea why it would miss these.

I believe the "ucase" tag is incorrect since its looking for a number but I am not sure what to put in its place and if I use something else should I dim it as ""?

I've attached an excel spreadsheet which contains a sample to this problem. I've also attached what it should look like if my code worked.

View Replies!   View Related
Pivot Table: Adding A Percentage Field As '% Of Subtotal'
In the attached Excel file, there is a pivot table.

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).


View Replies!   View Related
Extracting Source Data From A 'text-only' Pivot Table
I work in my Client's office and assist in settling construction disputes. Part of this work is to browse/search their server for documents that may assist in strengthening their case.

During this review I have found an excel document which is a text-only version of a pivot table, ie someone has done a copy, paste special, values into this sheet. I need to extract the original source data from this table back into the list format, as the original source of the data cannot be located

The row titles on the left are activity descriptions, the column headers are dates and the data in the body of the table is hours. As an idea of size the data is spread over 213 columns and 45 rows. Their are more blank cells in the table than entries.

What I would like to do is create the data in it's original form ie

Column A; Date
Column B; Activity Description
Column C; Hours

and have a separate row for each instance of an entry of hours from the pivot table.

View Replies!   View Related
Pivot Table Show All
I am trying to form a macro in VBA that will basically uncheck the "Show All" function of the Pivot Table filter user interface, and then select only the one PivotItem that I want. In context, I have about 50+ different project numbers, each with a different worksheet and its corresponding pivot table. Here is what I have so far:


Dim pvtitem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project #")
For Each pvtitem In .PivotItems
pvtitem.Visible = False
Next
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project #")
.PivotItems("525064").Visible = True
End With

When I try to run it, it gives me the error: "Unable to set the Visible property of the PivotItem class". It looks like the error occurs in the loop part of the macro.

View Replies!   View Related
Trigger Event From Pivot Table Drop Down
I am trying to write a worksheet_selectionchange macro in which the change is initiated only when certain cells are changed (B6:B9 and X9). I do not want the change to be initiated when any other cells on the worksheet change. B6:B9 and X9 are drop down boxes and the values within the drop-down boxes is variable.

View Replies!   View Related
Clearing Pivot Table Drop Down Entries
I was wondering if there is a simple way of deleting past entries from a drop down list for a pivot table. I have tested my spreadsheet and now want to create a 'blank canvas' for users.

View Replies!   View Related
Show Pivot Table Data By Name
I have a list of Insurance payers - 20 or so. They are listed in a Pivot Table on sheet2 with rate data and such. On sheet1 I have a listbox with the Payers listed and can be multi selected by the user. I wish to have the user select some payers in the listbox, goto sheet2 and view the Pivot Table only containing those Payers selected from the listbox.

View Replies!   View Related
Pivot Table 'views'
I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot.

View Replies!   View Related
Pivot Table Not Showing Data :: Selecting From Drop Down
i have a pivot table that shows the customer names. when i select the customer drop down box i can all the customer i want to see. but in the table itself there are some customer not showing. this is the first time this has happened


View Replies!   View Related
Pivot - Can't Show Field Lists
I don't know how I done it. But I have managed to totally goober up my pivot table settings at a global level.

Start with a data worksheet and do Data | PivotTable and Pivot Chart Report... and just click the Finish button and you get a skeleton with the grey "Drop Column Fields Here", "Drop Data Items Here", "...Row...", "...Page..." and blue highlights around each. I don't know when -- but it must be recently, perhaps even this morning... I did something that turned off this behavior. Now I don't see the grey messages. Now I don't see the blue outline. Normally, if that were the case, one simple clicks on the Show Field List buttons and voilá. But neither on existing pivots in workbooks that have always been well-behaved and new pivots too, I cannot get the Show Field List buttons to work! Neither the default feller on the PT toolbar, nor the same button on the popup menu that you get when right-click the PT. Even more amusing? The button(s) are not disabled. If I click somewhere off the PT, then yes, the buttons disable.

Click back on the PT and the button on the PT toolbar "enables". They just don't do anything.

Troubleshooting failures so far...

View Replies!   View Related
Simplified Drop Downs
Is it possible for data like "ABC /sdfsd/sdfs/sdf/sdf/sdf/" to be viewable in a drop down but when selected to appear only as "ABC" in the spreadsheet? I need to be able to see the full line of data when selecting my choice from the drop-down but for readability-sake in a printable spreadsheet only want "ABC" to be displayed.

View Replies!   View Related
Multiple Drop Downs
I'm working with 2 drop down lists created through Validation.

I'm trying to make this scenario work.

If A is entered in B1, then the drop down list in C1 would appear in D1.

If B is entered in B1, then the drop down list in C2 would appear in D1.

List C1 has 12 choices.

List C2 has 8 choices.

I'm making a data entry tab so for the sake of simplicity I only want one list to show up at a time depending on the variable A or B

View Replies!   View Related
Using Two Or More Drop Downs With VLOOKUP
I have an analytics sheet where I have a client name chosen from a drop down via validation. The problem I am running into is that while I want information from a data table to populate a cell based on the selection from the validation, I also want the cell to populate based on a second drop down box (basically selecting a time frame). Take the example data table below:

Client Jan 2009 Jan 2009 Target etc etc
Client A 1111 1290
Client B 900 750

I would like the user to be able to select Client B and then, from a separate drop down, also choose, say, Jan 2009 Target and then another cell would populate with, in this case, 750.


View Replies!   View Related
Totaling With Drop-downs
I have a sheet that has drop-downs with totals affiliated with each option. This is a time recording sheet and I would like for each drop-down type to be totaled accordingly. Any help with an equation would be greatly appreciated.

If you have any suggestions, I can send you the document for review.

View Replies!   View Related
Drop Downs And Adding
trying to shred the last bits of fat for the summer and thought I'd try a Keto diet, this depends heavily on not eating very many carbs, as well as keeping your total Kcal intake below maintenance.

This makes for a fairly tricky diet, as most things you generally eat contains alot of carbs, so I thought I'd make planning my meals easy, by creating an excel sheet, with drop down menues for different foods/drinks as well as their nutritional content.

I''d like it to look something like this, with the dropdown list under "Food" ....

View Replies!   View Related
IF Then Statements And Drop Downs?
can you have col A contain a drop down and then column B include an if then statement that would result in another drop down? IE. Column A has drop down with options of "A", "B", and "C", if the user selects "A", then a drop down would appear in column B containing options "1", "2", and "3". But if the user selects "B" in column A, then a different drop down appears in Column B with options "Yes", "No", and "Maybe"...etc....

View Replies!   View Related
Show Field Contents In Pivot Table
Can I do something so my pivot table shows Manager name in all appropriate cells (eg. agomes is A3:A4 and bschaefe in cells B5:B13)? ........

View Replies!   View Related
Show Detail Event In Pivot Table
When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action?

I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.


View Replies!   View Related
Show Pivot Table Detail Macro
I have recorded the below macro to select a pivot table field (on demand) and show the information on anthother sheet tab.

Obviously if the order of the fields change then the macro is buggered, any ideas so it only selects the on demand field and shows that info.

Sub Macro1() ....

View Replies!   View Related
Make Multiple Name Show Up Only Once In 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?

View Replies!   View Related
Pivot Table, Show Empty Rows
In my Pivot table I have 3 fields in the "header - section" of each row
There is also the possibilty in the Page section to choose between subjects (eg physics, chemistry, biology etc) When all rows are displayed there are 68 in total

When I choose Physics there are about 30 customers that have a value in 1 of the rows. Excel shows 30 rows, but I would like all 68 row to be shown, because these are the values that are important to me. I have tried a lot of settings in the pivot table but can't find the correct 1.

What happens a lot is the the rows are "multiplied", meaning that the 1st row header has every combination of the 2nd and 3rd and so one. Which setting is needed to get what I want?

View Replies!   View Related
Show Budget VS Actual In Pivot Table
I have weekly budget report (for the entire year) that I put in a Pivot Table with approx 20 different row labels. The actual revenue numbers come only once a month (several different reports make up the actual revenue numbers). I need to put the actual numbers on the same row as the budget numbers and I can't figure it out.

For example, for the first three months of the year, I have the pivot table showing everything perfectly. In February, I get January's actual revenue numbers that now "replace" the budget numbers for January. If this was a regular table, I could easily overwrite these numbers, but with the pivot table, I can't get these two fields to line up. For now, I have the Actual Revenue numbers on the Rows above the Budget numbers for their respective months/quarters.

View Replies!   View Related
Show Percentage Subtotals In Pivot Table
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.

View Replies!   View Related
Pivot Table: Muscle Memory With The Drop Down Choice Boxes
I am somewhat of a novice with Pivot Tables. One problem I am having that no one seems able to help me out with is that when I update and refresh my pivot table, it seems like it has a muscle memory with the drop down choice boxes.

For example:
Lets say I have three customers that I have labeled 01 Target, 02 Walmart and 03 Bed Bath and Beyond. If I want to change 01 Target to 02 Target and 02 Walmart to 01 Walmart, it works in the pivot table and shows the proper data but if I try to choose the customer from a drop down box, it remembers the customers as labeled as they were before I made the changes AND the current look.

I will see: 01 Target, 02 Target, 01 Walmart and 02 Walmart in the drop down box but the data will only show up in the pivot table associated with the proper customers as they are labeled in the data source.

View Replies!   View Related
Drop Downs & Filters
First post so please excuse me if this has been covered before but I am trying to get the results of a dropdown menu in one worksheet to become the criteria for a filter in another worksheet (both in teh same workbook).

I thought I could just add a reference to the dropdown cell into the autofilter (in VBA editor) but it doe't seem to be that simple.

Any ideas?

View Replies!   View Related
Macro To Show Pivot Table Details..Any Ideas?
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 Replies!   View Related
Show Numbers Greater Than An Amount In A Pivot Table
Is it possible to just show numbers greater than an amount in a pivot table?

View Replies!   View Related
Show Message If Empty Cells In Pivot Table
I'm trying to check and prompt a message box if there is a empty cells found in the pivot table.

If Activesheet.PivotTables("PivotTable1").NullString = "" Then
MsgBox "No Match Data Found"
End If

I have try out the code caption above but not the result as I want.

View Replies!   View Related
Easily Hide/Show Pivot Table Items
I work at a trading firm and use pivot tables to report on the success of traders on a daily basis. I add daily trading data to a raw data table that powers a set of reports. In one report I want to view MTD stats for a filtered group of 10 traders. The issue is that if I add a set of daily data that includes a new trader name, it will automatically be pre-checked and added to this report (and this happens daily). The only solution I came up with is to add another column in the raw data table that would allow me to group these traders and then use a page filter to include only them. This will work but I'd rather avoid adding columns to an already unruly data table (and would like flexibility to periodically define and track an arbitrary set of traders).

View Replies!   View Related
Hide / Show Pivot Table Areas On Condition
I'm just stuck on a final thing......I would like the pivot table to collapse based on the outcome of a formula. For instance I've got a project that has A, B, C, D as component parts, these parts all have a percentage work completed. If let's say A is a 100% complete I would like to automatically collapse A, so it will just show the summary for A. Obviously this can be done by hand, but since there is quite a lot of data I would like to automate the proces.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved