Tag: pivot-table

Why is some of the colorization of cells not occurring, and is there a connection between that and the “Cannot perform runtime binding” err msg?

I’m getting the following err msg: This is occurring in this code: private bool GetContractForDescription(string desc) { int DESCRIPTION_COL_NUM = 2; int CONTRACT_COL_NUM = 7; bool contractVal = false; int rowsUsed = _xlBook.Worksheets[“PivotData”].UsedRange.Rows.Count; int colsUsed = _xlBook.Worksheets[“PivotData”].UsedRange.Columns.Count; string colsUsedAsAlpha ReportRunnerConstsAndUtils.GetExcelColumnName(colsUsed); string endRange = string.Format(“{0}{1}”, colsUsedAsAlpha, rowsUsed); Range sourceData = _xlBook.Worksheets[“PivotData”].Range[string.Format(“A2:{0}”, endRange)]; // This is blowing […]

Deselecting Pivot Table Items Error

I’m trying to use the code in this link, but I keep getting the runtime error 438 “Object doesn’t support this property or method” for the line ActiveSheet.PivotItems(1).Visible = True Does anyone know what’s going on? Full Code: Sub DeleteAllFields() Dim i As Long ActiveSheet.PivotItems(1).Visible = True For i = 2 To ActiveSheet.PivotItems.Count ActiveSheet.PivotItems(i).Visible = […]

Finding averages of subtotals in pivot tables

I am currently working with a pivot table that contains hours logged by individual people for specific tasks and I’m trying to find out the average time taken in man hours for a specific task types but I’m having a lot of difficultly finding a way to easily do it with large amounts of data. […]

C# Conditional Formatting Pivot Table loses formula

I’m having problem with a Conditional Formatting with Pivot Table. I get the range of the pivot table, my code display the Traffic Light icons, but if i show the pivot table details (expand or collapse) in both ways, the icons dissapear. I saw that the Conditional formula changes when I do that, but i […]

Adding set of variables to different fields in pivot table using VBA

I am VBA novice trying to add certain variables to the values field (but this question can be expanded to other pivot table fields, like rows or columns fields). I have weeks of the year that I would like to add all at once using VBA rather than one-by-one by hand (dragging and pulling). I […]

Automatic update of calculated fields (Excel Pivot table)

I am creating a top 10 product by sales calculation off of a pivot table I am running. My question is, I would like this table to populate automatically to the latest week when I refresh the table and am not sure how to do this. Furthermore, there are some additional calculations like YoY growth […]

How to reduce pivot size and get only the required data using view?

I have created a pivot excel file with filters in SQL views connected to the excel, its fine but the data in the pivot excel is too large to send in email (or) it has more unneccarsy data. I need only the filtered data from the pivot excel file, so I tried copy and past […]

Taking the top values and averaging from data list in Excel

I have a data list in Excel, I am looking to take the top 3 values for each number, and get the average for those 3 values quickly. I often work with lists of up to 50,000 lines which at any one time could convert to over 10,000 different column A numbers. I understand basic […]

Transposing tables in a pivot to get count of values

My input is this OCT NOV DEC P1 P1 P1 P1 P1 P2 P1 P1 P3 P2 P1 P3 P3 P2 P3 P4 P2 P3 The P1 and P2 can change. I want to set up a pivot so I can see the count of each project. Is this possible? My desired output is OCT […]

How can I count “Pass” And “Failed” on table to generate Pivot Table?

For example, this is my table. The data has “Pass” And “Failed”. How can I count how many pass and failed and generate to Pivot Table.

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.