I'm trying to create code where a user can input criteria into a cell then run a macro. The macro would then use that criteria to filter the pivot table. I have attached an example. The first portion, using date to filter the current page - pivot item is working. I need to creating the code to use the "supplier" to filter the pivot items on the row. As a note, both the Date and Supplier cells are named references.
View 2 Replies
I have a spreadsheet with a pivot table that is based on a table within an SQL Server database. Every month a stored procedure is run creating a new table. Using the pivot table wizard I can modify the underlying query to access the new table. The pivot table functions properly with the new data.
I also have a macro that I created that will cycle through all the combinations of page field pivot item values, setting the page field currentpage, do some calculations, and save the results.
This is bombing because it seems that some old date values from the previous month's table are still in the pivot items and when my macro attempts to set the page field's currentpage to one of these values it generates Run-time error '1004' Unable to set the _Default property of the PivotItem class.
I have tried putting a pivotcache.refresh in but that doesn't seem to help.
Change "refer to" in Named Range. I have a named range myrange referring to A1:A5. I have a condition for B1, if B1= annual then myrange should refer to C1:C5 and not A1:A5..
View 2 Replies View RelatedI have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.
However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class
The code where the debugger stops is the last line below here.
lngKpi = Sheets("Helpfile"). Cells(lngRow, 2)
Sheets("Charts").Select
ActiveSheet.ChartObjects("DK").Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi
I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.
I was wondering why a pivot table will allow you to tick and untick pivot items that are not visible?
My pivot tables have a lot of data and someone may wish to only see the pivot items that are shown in the pivot table in its currrent view. Is there a way in VB to achieve this?
In my attached example when you suntick the Media 'TV' then look in the 'Channels' drop down you can still see the tick boxes for TV channels 'ITV' and 'TV_BBC1' (I know why they are still ticked, I'd just like them not to be visible).
I'm not grasping the Pivot Table correctly. I've written code to create a sum of values based on a worksheet. Specifically:LocationIDDeptSum of Hours Worked. Location, ID and Dept are rows. This effectively provides the aggregate values that I need based on the row groupings.Here's where this is falling apart. I need to create a new worksheet based on these values. I assumed the three row values - Location, ID and dept - would be in a hierarchy. It's possible they are, I just can't figure out the object model.
When I loop through the PivotItems collection of the PivotFields("Location"), I get what I need. However, I'm unable to determine how to loop through the child values (just for that location). PivotFields("ID") returns all IDs. I can't figure out how to return only the child entries for each pivot item. GetPivotData hasn't been very useful for this. As far as I can tell, GetPivotData, while its return type is listed as Range, throws an error when more than one cell is returned. Worst case, I suppose I can just parse the data in the DataBodyRange of the pivot table - maybe not, I haven't tried that. I'm hoping there's a way to iterate through these collections, but based on what I've seen from Google searches, there may not be. Does my pivot table need to be rearranged? I suppose I could also just dump this data into a data table
In the attached workbook, I have identical sets of data in column A separated by an blank cell. I need a macro for the user form that i need to build. The code needs to find the end of each named range, (Range1,Range2 and Range3), to insert blank row at the end of each range and to copy the value of the textbox from the user form in each row so to keep the sets of data identical.
View 9 Replies View RelatedI have a named range on a hidden sheet which populates data validation lists on 'sheet 1, cells b5-b55. My question is this...Is it possible to, upon entering a new item in b5 (for example), to have that item automatically added to the named range? (preferably through a button on a pop up form that opens w/ the question 'would you like to add to the list?, similar to an access form)...I've seen one example in the past which I found with a link from this site, but couldn't make it work. If the answer to my question is yes, could you also suggest a location of an example if you know of one?
View 14 Replies View RelatedI'm trying to insert a calculated row in a pivot table. I keep getting the error message 'Pivot table items can only refer to items in the same field as the calculated item'
Below is the format of my data:
Region(All)
Sub Division(All)
Sub Cluster(All)
Cluster(All)
A/C Level 3(All)
A/C Level 1(All)
A/C Level 4(All)
A/C Level 5(All)
Division(All)
Values
A/C Level 2Sum of FY11Sum of FY12Sum of FY13
Net Revenues 100 200 300
Op Exp 50 40 30
Calculated Field50 160 270
Attached is a worksheet example, of my beauty salon with 5 therapists.
The input sheet - is the shhet into which I input data on a daily basis.
Weekly inc - This sheet shows the total that each therapist made in apivot table based on the input. The week numbers are 1 to 52 (or 53) depending on each financial year (07/08, 08/09 etc).
What I want to do is either have another pivot table or to add an calculated item to the weekly inc pivot table showing me how much commission each therapist took each week? This is to be done automatically by excel.
This commission is calcluated by using the target comm figures - E.G. Leanne's target is £480 - so she has to earn £480 a week before getting a commission. So if in a week she earns £500 - she will get a commission on £20 (£500 - £480). The commission is 10%, so she will earn £2 that week.
I'd really like to include the yellow fields below inside the Pivot Table but I can't find a way.
On the collumn Label I have the Category, which can be Budget or Forecast. Then on the Values Field, I sum the Revenues.
RevenuesCategoryVar GrossBudgetForecastK €%Total285.264142.548-142.715-50%
I want to calculate the difference between Budget and Forecast, in € and %. I was trying to use a calculated Item but whenever I place the cursor on the Budget, Forecast or Category cells and try "Calculated Item", I get an error message saying that the Item cannot be modified..
I have a data that consists of months, forecast and actual.
I've created a pivot table where; months is on the row, forecast and actual is on the values
I wanted to know the percentages between forecast and actual (forecast / actual) to see how it perform each month.
How do I go about inserting a pre-defined named called "percentages" on pivot table with calculated field or calculated item on pivot table itself?
I have 2 columns. One with a store number. The other with an item. I need to get the count of each item for each store. So:
Store, Item
1, Cashier 1
1, Cashier 2
1, Cashier 2
1, Photo PC 1
1, Cashier 1
1, Cashier 2
2, Photo PC 1
2, Cashier 1
2, Cashier 2
2, Cashier 1
2, Photo PC 2
2, Photo PC 2
2, DriveThru 1
3, Cashier 1
3, Photo PC 1
3, Photo PC 1
3, Photo PC 2
I would like to get to
Store, Cashier 1, Cashier 2, Photo PC 1, Photo PC 2,
1, 2, 3, 1, 0
2, 2, 1, 1, 2
etc....
How can this be done?
I have cut down my problem to its simplest form - in the real world my spreadsheet is significantly more complex but the attached spreadsheet demonstrates it nicely!
The data is straightforward. PT1 shows the data without a calculated item whilst PT2 is the same PT but with the addition of a calculated item for GP (i.e Sales - COS). Unfortunately adding this has resulting in the PT growing and adding "ilogical" combinations of data that didn't exist in the source data.
In my current project I have "cheated" by using a helper column and filtering on this using the Worksheet_PivotTableUpdate event to hide the unwanted rows but surely there must be a way of removing them "properly".
Another problem, and I guess this is just the way it is, is that my PT takes ages to calculate presumably becuase it is calculating this formula for every combination of values?
I have an issue with a calculated item in my pivot table, because in the totals and subtotal it shows the sum of the column but I want to show the formula that I've specified to the calculated item.
For example, in the attached file, I have in rows the field "name" that has four values (A,B,C,D), in columns the field "Groups" that has "G1" and "G2". I add in the rows the calculated item "G1/G2" that has the formula G1/G2. The problem is that in the total the column "G1/G2" doesn't show the division of the total of "G1" and "G2".
I have a Pivot table.
Layout:
Rows: City, Restaurant, Cashflow
Column: Date (Month/Year)
DataField: $Amount
"City" might be 7 cities
"Restaurant" may be 32 restaurants, some in some cities; but not other
"CashFlow" can be either "Revenue" or "Expense"
The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.
Now.... the trouble...
I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries.
How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?
Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?
I'm trying to get the parent item name of a clicked cell (e.g. when I double click on field "Commessa 2" I'd like to get "Region 3" and pass it to another sheet)
Here is my code:
[Code] .....
When I run this code, I get the message "Unable to get the ParentItem property of the PivotItem class"
Attached Image : Capture.PNG‎
I am trying to add up the same item until it changes. I have attached the file with the desired outcome in the 2nd column.
View 1 Replies View RelatedI am having an issue with my macro code. Basically I am trying to filter a pivot table by selected criteria. The code seems to be correctly filtering the pivot table but the table appears with no data in it unless I manually refresh the table. This defeats the purpose of automating with a macro. I have attempted a "work around" of just refreshing the pivot table after the filtering is complete but that was unsuccessful. Here is the macro code:
Sub BigReportMacro()
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField
Dim E
Sheets("Pivots").Visible = True
Sheets("Pivots").Select
'Refreshing all the Pivot Tables
[Code] .....
See attached for an example. I'm trying to use the SumIF funtion with a named reference as the criteria. Here is my formula. =SUMIF(B10:F10,"<MinReq",B10:F10) It keeps returning 0 even though there is valid data.
View 3 Replies View RelatedI am trying to number rows based on criteria. when the value changes add a 1. This is what I need to have happen
Apple - 1
Apple - 1
Apple - 1
Orange - 2
Orange - 2
Plum - 3
Plum - 3
I can get an if statement to number everything 1.
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:
View 14 Replies View RelatedI have the following formula which i'm using to indirectly reference a worksheet. The worksheet i'm referencing includes named ranges that i'd like to call on but i can't get the syntax right.
Code:
=SUMPRODUCT(--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")>=D5),--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")
I'm trying to use a countif formula with a named reference but the formula is not calculated correctly. My example would be: countif(A1:A5,<max) with max being the named reference. I also tried various options using quotes around the named reference (ex. "<max") but that doesn't work either
View 3 Replies View RelatedI have goal table on table A and i want it data fill to tabel B with constant sum. maybe like sudoku in sum right and sum down.
View 1 Replies View RelatedI am trying to change the caption of a toolbar item but for some reason it does not work.
Application.CommandBars("Format").Controls("Item...").Caption = "I&tem..."
However,
Application.CommandBars("Format").Controls("Item...").Delete
for example works just fine
Suppose I have a department List, and I made a list from that column (Department List), and I enter more staff and selected a department for them during registering, now one of the Department List Item change, How is it possible to change all the entry which has that department?
For more info see the attachment.
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I have a named range called ReportMonth and I want to embed it into this Table reference to make the number of columns that are summed dynamic:
=SUM(tblFcastTemplate[@[Column1]:[Column28]])
I tried ="SUM(tblFcastTemplate[@[Column"&(ReportMonth+1)&"]:[Column28]])"
