I am using pivot table for my customer aging which a sample is enclose. What I want to do is to Highlight the field " Customer Name " in colour if the the Field " Type of guarantee" is other than 0. Also a message of " Credit limit Exceeded" if the outstanding is more than the Credit limit.
View 2 RepliesI have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). What I now need to do is add borders to the cells in columns “D:G” only if the cells in “B5” and downwards are not empty. I have tried doing it with conditional formatting but there are some other rules which contradict this setting.
The idea is to make the cells in columns “D:G” look like they are a natural extension on the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column “B” (staring with B5). I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my “real” file, the length of the table is changed whenever I select a different “page” of the PT.
filter my pivot table using a cell value, rather than manually selecting from the dropdown.
The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).
Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?
I'm trying to make a pivot table that can compare sales based on the whatever month/year/salesman combination I give.
Ideally, I'll be comparing the sales data for 3 seperate months. Can I make a pivot table where I can make different filters apply to specific columns?
I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:
Sub FilterDates()
Application. ScreenUpdating = False
Sheets("PnL").Select
Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
Have following table:
No. Be Country
1 D GB
1 C DE
2 B DE
2 A GB
I want to present it as follow:
GB DE
1 D C
2 A B
Did not solve it with a pivot table. Not either with VLOOKUP combined with IF. I am thinking in the direction: If 1 in the first column and GB in the third, return the D. But I am not getting it right.
One problem I am however having is with a spreadsheet similar to this example found on another site. On the pivot table sheet. The link is http://www.kan.org/tips/files/sumproduct_advanced1.xls
The problem Im having is, for a particular number that is column D I want to hide what is column A. For example for every number "0" that is found in column D, I want to hide its heading in column A along with the rest of the numbers that are in column D that are associated with the heading as well.
I am creating a data base to track Planner compensation. Many of our accounts have 2 planners on the same case. I am trying to learn how I can create a single list of all the planners (from "Partner 1" and "Partner 2") headings which will then total their compensation in a Pivot Table weather their name is under the "partner 1" heading or "partner 2" heading. See the attached example.
View 2 Replies View RelatedI copied and modified the pivottable code from http://www.ozgrid.com/VBA/hide-pivot-fields.htm. I am getting a "Run time 13" error on the line I colored purple. I tried removing different "Dim" statements to make it work and I'm having no luck.
Sub PickUpPivotTable()
Dim pt As PivotTable, pi As PivotItem
Dim lType As Long, lHarn As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
Set pt = Worksheets("Monthly Pivot Summary").PivotTables("MonthlyPivotSummary")
strCri = "P/U"
For Each pi In pt.PivotFields("Type").PivotItems
lType = pi..........................
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 8 Replies View RelatedMy worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies View RelatedI have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
Every time I make a pivot table I change the formatting ie column width but when I press refresh it goes!
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
formatting the sub-row header of the table also changes one of the columns.
View 6 Replies View RelatedI have a table (attached example with only a SMALL part of all data) I would like to transform this into pivottable format without copying and pasting. In row 2, all of the numbers pertain to years. Column B has the number of years in force for each property account (Column A). I
View 2 Replies View RelatedHow can you visually format a column with multiple values in a pivot table? Say you have month in the column section, and three values in the value section: budget, actuals, variance; I want to visually seperate each month's set of data.
View 1 Replies View RelatedI have a sheet that comes from a pivot table that I have to format each month. It's a pain I was hoping a macro might be able to do. I was told this is the place to come for this. I have attached the the sheet unformatted and formatted. Note, there are 2 more columns on the finished sheet I add in to show totals. Also note col A has to be re-ordered each time (Safety, Environmental, Security, Vehicles, Operations).
Not sure about the complexity of this. Let me know. And let me know if I need to supply more info.
i m trying to format a cell so that it colours in red when a specific text is chosen in another cell, ive tried conditional formatting but I cannot get it to work. e.g column A has a drop down list, if GB or IEN is chosen in that cell then cell in column K will become red. (as if these two are chosen then they have to input a number into column K). Once they have inputted the number into column K i want it to go back to its original format. Or if they dont select GB or IEN then it would stay in its original format aswell.
View 2 Replies View RelatedI want to be able to delete certain rows based on multiple criteria. If data in the row does not meat the crieteira, it shud be deleted. I have attached the excel file sample in which I want to keep the highlighed rows and detele the rest. Its base on Origin city and Destination city combination. Eg: If Origin city="A" and Destination City = "B", I want to keep the row. Like wise for all highlighted rows. but if the Origin City = "B" and Destination City = "U" I want to delete the row.
View 9 Replies View RelatedI'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?
View 4 Replies View RelatedI would like to have a pivot table that will count the number of responses I get for a certain question
For example on the attached book I would like it to look something like:
a
blue 1
red 1
yellow 1
green 1
aa
green 2
yellow 1
and so on, I am sure this is possible just not sure how to make it happen.
When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.
Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?
For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.
I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.
If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.
Is there any way to change the format for pivot table sub data? Or maybe use a marco for it. The data table that appears when you double click any field on the pivot table range.
View 6 Replies View RelatedI am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.
Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.
Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.
I have a listbox that lists some items from a spreadsheet, but what I cannot figure out is how to have the font color of the items be RED if the value if negative, and default BLACK when positive...
how to alter the code to accommodate this?
VB:
With UserForm1.ListBox4
.ColumnCount = 5
.ColumnWidths = "2 cm;3 cm;3 cm; 3 cm; 3 cm" [code].....
I'm trying to create a formula that tells me the following:
If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.
Hi All, I have 2 pivot tables (PivotTable1 and PivotTable2) which both source from the same pool of date.
The difference between the two are that I have a different report filter criteria.
For one the heading is "day", for the other it is "night". Each colum of data has the same entry range (names of staff) and I want to be able to link the two tables so for instance -
If i select "Paul" from the (day) report filter on PivotTable1, I want the (night) report filter on PivotTable2 to also change to "Paul".
I have a report to fill that looks like this:
Table 1:
HEMANITOBAMANITOBA SK12
I want to fill it up with data from this pivot table:
HEIntervalManitobaManitoba SK11-10522-8423-8224-6125-5826-6427-6828-5929-53210-47211-53212-63221-7522-8023-7924-7825-7726-7627-7728-8229-86210-86211-86212-722
So basically what I want to do is for example from Table 1: If I want to find the what goes in cell in B2 (1, Manitoba) then I want to go to the pivot table find 1 which is in the first column(HE), there are 12 intervals to each HE(as seen in column 12), I want to take the average of all the HE with 1 corresponding to Manitoba (the values in Column 3) and to be put into B2 in Table 1.