Slicer Buttons Not Refreshing?
Dec 3, 2013
Im using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?
View 1 Replies
ADVERTISEMENT
Mar 28, 2014
I' have two slicers that each control two pivot tables. The slicers both are for the same field ("Department") but I guess the pivot table structure differences don't allow me to simply have one control all four tables.
Since both slicers contain the exact same options from the same field on the same origin table, I would like to problematically ensure that when department "A" is selected on slicer1, it is also selected on slicer2. When departments "A, B, & C" are selected on slicer1, the same are selected on slicer2. When filters are cleared... you get the picture.
View 5 Replies
View Related
Apr 10, 2014
I have two slicers, say slicer 'F' that slices on fields 'x', 'y' and 'z' and slicer 'E' that slices on fields 'a', 'b', 'c'.
I select these values by using a very simple macro. For instance to select z in slicer "F", I have the following code:
With ActiveWorkbook.SlicerCaches("F")
.SlicerItems("x").Selected = False
.SlicerItems("y").Selected = False
.SlicerItems("z").Selected = True
End With
However, I want the selected fields to have more flexibility than which is possibly by coding them in VBA. Ideally, I would want to write the selected fields in a cell in Excel (also if there are more of them!), then let the macro retrieve these fields and use them to apply to the slicer. So I write in a cell slice on X and Z, the macro picks this up and performs this operation.
Is this possible?
View 1 Replies
View Related
Jan 31, 2014
I have a list box contains of customer name and I used that to set up formula based on your selection in the list box. However, I wonder if there is a way that can select multiple customers in the list box and it will add up all that customers from your multiple selection like in the pivot slicer. I don't know if we have any formula that can do that complicated thing.
View 2 Replies
View Related
Jun 12, 2014
In my pivot table I have "Due Date" with is a column label.
I grouped the dates into weeks by using the "days" grouping and using 7 days.
I created a slicer to allow the user to select with week they want, but the slicer is sorted as though it's a text field and not a date field.
Example:
6/1/2015 - 6/7/2015
6/13/2016 - 6/19/2016
6/15/2015 - 6/21/2015
6/16/2014 - 6/22/2014
I want the slicer to sort by actual date.
View 1 Replies
View Related
Dec 31, 2013
I am currently working with a pivot table someone else created. The data set has dates only, in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.
Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.
How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
View 3 Replies
View Related
Jun 12, 2014
The main objective of the code is to create different workbooks that are going to be send to different people(with only their data).
I am using the filter to delete the data that I don't need and then create a new workbook, save it and close it. This has been working fine.
The issue is that I have two slicers in the 'DistMenu' sheet. When I open again the newest workbook, they slicers are disconnected to the pivot tables. This can be fix manually by selecting the slicers --> PivotTables Connections etc
I use the macro recording to get a code and work from there, but is giving me error '1004' "Unable to get the PivotTables property of the Worksheet class".
View 1 Replies
View Related
Jan 8, 2014
I need to transfer selection(s) from slicer Country to Country1 (they cannot be connected due to workbook functionality).
User can make a single or a multiple selection in the country slicer.
So basically using CUBERANKEDMEMBER I got the output from slicer Country (in cell A1 for now) and used it as input in VBA for slicer Country1
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]")
I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)
Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.
So, if the user selected 2 countries I would need to run a following code:
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]", "[01_Feed].[Dosage].&[" & Range("A2"]")
Now, is there any way to loop this within the array, how many cells it should take?
For i = 1 to ..
code from above
Next i
won't work because then it only takes the last value, so I kind of have to loop it within the array.
View 2 Replies
View Related
Feb 10, 2012
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is there a quicker way?
View 6 Replies
View Related
Mar 14, 2007
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
View 9 Replies
View Related
Nov 25, 2013
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
View 4 Replies
View Related
Feb 20, 2014
I created a macro to create the beginning of a pivot table- just the rows and slicers, because when I tried making a longer macro to create the entire pivot table (formatting, etc) I couldn't get the macro to work.
So I do get all the vertical rows I want, and I do get all the slicers I want. But I get blank space. I think this is because the pivot table is created from another tab, and the length of data in that tab will vary from file to file (I'm doing another 30 or so of these, all with varying amounts of data, but the same data categories).
Anyway, I get slicers, but in addition to getting the categories I want, I also get "(blank)" below the names in the slicers. Is there a way to add some code to the macro to remove the blanks so I don't get this field?
I have copied and pasted the macro below, with identifying data changed.
View 4 Replies
View Related
May 2, 2013
I have multiple pivot tables with different data source. I wish to have one slicer which control all the pivot tables. I would have one common colum for all the pivot tables which is the one i wish to control for instance the person in charge. Note that all my pivot tables are from different data source. how to do it?
View 1 Replies
View Related
Sep 26, 2013
I have two pivot tables, both of which source the same sheet of data. Each record in the data has two fields, Region ID of person and Region ID of facility, that reference the same list of Region IDs via vlookup (it's just numbers 1-12). Either one or both can be blank. One pivot outputs counts by person Region ID, and the other, by facility Region ID.
Using a combination of nested IF statements and vlookups, I tried making a third Region ID field that could be used as a slicer to control both tables, but what I end up with is an undercount in one of the tables. The only thing that works so far to output the correct counts is having two separate slicers, the facility Region ID being the slicer for one and the person Region ID being the slicer for the other. If the end user wants to see counts for one Region ID, they have to manually set one slicer equal to the other. But what we want is just for the end user to be able to control both tables just by pushing a single number, Region IDs 1-12.
I can make one of the slicers hidden but then how do I get the hidden slicer to automatically select Region ID values equal to the nonhidden slicer? Alternatively, I could somehow program a combo box or list to control the two different slicers, then the slicers would be hidden and the user would see only the combo box/list. (I guess in either of these alternatives, I could just use a report filter instead of a slicer; either way, I still have to get items in one to automatically select based on the user's selected items in another).
FYI, some of the options I looked up involved PowerPivot, which I do not have access to. VBAs/macros are ok.
View 2 Replies
View Related
May 16, 2014
I've inherited a workbook that has about two dozen or so pivots spread over a few worksheets that source data from an analysis services cube.
There are multiple slicers attached to the various pivot tables and charts
Some of the reports (worksheets) have a business rule that certain filters need to be selected in specific combinations for the data that's returned to make sense.
eg the fact table holding the measures has a billmonth and processmonth that's linked to role playing date dimensions. For the report to reconcile the data correctly both the billmonth and processmonth need to be set to the same value. So if I select 2014-03 on the billmonth slicer, I need to set the processmonth slicer to 2014-03 as well. And there's another pivot on the same worksheet that's linked to a different fact table that's at the year grain, and for that bit to make sense it should be set to 2014.
My task is to simplify this by propagating the billmonth value to the processmonth and the billyear, but I haven't played with vba in about 7 or so years, so I'm very out of practise.
How do I use vba to monitor a slicer for changes? and if it does change how to set another slicer to a dynamic .Value? It can be assumed that the value will always exist in the downstream slicers, if it doesn't the user has bigger problems than an excel error.
I've recorded the macro of me selecting the same date on both slicers but it doesn't give me much to go on
ActiveWorkbook.SlicerCaches("Slicer_DimDateBill.DateHierarchyFinancial1"). _
VisibleSlicerItemsList = Array( _
"[DimDateBill].[DateHierarchyFinancial].[Fin Month].&[201403 FM09]")
ActiveWorkbook.SlicerCaches("Slicer_DimDateProcess.DateHierarchyFinancial"). _
VisibleSlicerItemsList = Array( _
"[DimDateProcess].[DateHierarchyFinancial].[Fin Month].&[201403 FM09]")
Also it should disallow multiple selects, is there code to monitor that as well?
View 3 Replies
View Related
Jul 19, 2012
I am trying to create a "dashboard" style report using a pivot table and pivot chart with slicers. All of these objects (table, chart and slicers) are in the same worksheet. However, the users of the report (read executive management) are not very Excel savvy and I want to protect the format of the report. So, I want to protect the sheet and only allow access to the Pivot table options. However, in order to make the slicers usable, it appears that I have to allow them to edit objects. This unfortunately means that they can then move those objects (the Slicers and Chart).
I am looking for a way to lock the slicers and pivot chart in position. I have already accounted for column width changes created by modification of the pivot table parameters by selecting the "Don't move or size with cells" option under PivotChart Tools > Size > Format Chart Area > Properties > Object positioning. This is the default/locked-out setting for the slicers.
View 7 Replies
View Related
Mar 15, 2013
I have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB()
' Copies VBA modules, Calls
Dim wbNew As Workbook, wbT As Workbook
Set wbT = ActiveWorkbook
On Error Resume Next
Kill ("PATHmod1.bas")
Kill ("PATHmod2.bas")
[code].....
View 2 Replies
View Related
Nov 1, 2008
I have been working with one sheet (call it tab A) that has 40-50 vlookup formulas that reference another sheet (call it prices) within the same workbook. Everything worked fine. I change my prices tab and everything auto changes in tab A.
Now I copied 'tab A' 22 times creating tabs B - W. I need to slightly modify each tab and based on my prices data, print out the appropriate tab.
Problem is now, when I change my pricing tab, tab A - W do not update. F9 does nothing. Rebooted didn't work. If I go into the formula and hit F2 to edit & return it recalculates to the correct data. But I can't get it to auto update.
View 6 Replies
View Related
Oct 12, 2007
I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.
Is this correct?
If so, is there another way to "refresh" the usedrange property without saving the workbook.
If not, is there another way I can determine what the usedrange actually is at any point in time?
View 9 Replies
View Related
Jun 13, 2014
="=ROUNDUP("&"50.42"&",0)"
I take this paste text only to get:
=ROUNDUP(50.42,0)
this wont work normally until i open the cell and press enter.
Any way I could do that for more than 1 cell at a time?
View 6 Replies
View Related
Jan 5, 2013
I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
View 5 Replies
View Related
Apr 6, 2009
I've built a spreadsheet with quasi-animation for a physics class on projectile motion. In it there's a animated chart showing velocity change as a function of distance. The chart has on it an ActiveX textbox that refers to a cell that contains a formula showing "V_y = {velocity}". I've tried a number of things to get the display of the textbox to change during the animation. If I break the code at the line that updates the cell referenced by the textbox the display changes. But I can't figure out how to get the display to update while the code is running.
View 4 Replies
View Related
Feb 24, 2009
One of my SUMPRODUCT formulae doesn't seem to be refreshing when i re-open the excel file.
=SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(TEXT(Data!$F$7:$F$2000,"mmmm")=A26),Data!$M$7:$M$2000)
If i open the excel all the cells with this similar formula show as 0. If i press Shift+Ctrl+Alt+F9 then they calculate.
I have another SUMPRODUCT formula on another worksheet in the same book that seems to be fine.
=SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(Data!$E$7:$E$2000=B7),Data!$M$7:$M$2000)
I can only assume that the reason why it's not calculating automatically is to do with the TEXT "mmmm" reference, is that correct?
Is there anyway i can have it autocalculate so that if i send this as a report i don't have to ask the ppl looking at it to please press Shift+Ctrl+Alt+F9 when they open it :P
View 9 Replies
View Related
Oct 16, 2003
Is there a code I can use to update pivot tables e.g every 10 mins?
View 9 Replies
View Related
May 1, 2006
I have some code that downloads prices for me in excel. However, instead of running this macro everytime I was wondering if it is possible to write a piece of code that would run this macro every x minutes?
View 3 Replies
View Related
May 17, 2006
I have been working on creating a form in Excel. I have already figured out how to have combo boxes that are dependent on one another. Now I have a question on that. Let's say that my user makes a selection from the first combo box. Clinical Services for example. The second combo box should now show only the values that pertain to Clinical Services (which it does, that part is working fine). So let's say they choose Retail under Clinical Services. However, now let's say that the user changes their mind and they go back and change their selection in the first combo box to Mail instead of clinical programs. The second combo box stays on the selection that they had previously picked (Retail) even though that value does not pertain to their new selection (Mail). How do I refresh this to delete their old selection in the second combo box (Retail)? If they change their mind of the first combo box and select something else the second combo box does display the correct drop down information, it just doesn't clear out the old selection.
View 4 Replies
View Related
Jul 1, 2014
I'm looking for a way to refresh my pivotchart and update my pivot table data every time that a certain macro runs. I could just delete it and make a new one everytime but that runs in to the problem of defining a new sheet every time. Is there a simple way to do this. say I my pivot chart is on "Sheet 7" and it there is only one pivot chart in the workbook and my source sheet is on "Combined Data". and at the moment the data range is "A1:N435" and say next time I run my macro the data range will add 20 rows of data and the next 25.
View 1 Replies
View Related
May 25, 2009
I have a oval which changes color using the color given in a certain tile, temporary called "Hi" . However the oval doesn't change color automatically with the tile changing, it waits till the user clicks the checkmark for the tile, I'd like it to do it automatic, but it doesn't. Is is possible to animate this script a bit more so it refreshes upon a change in the "Hi" title.... ?
View 14 Replies
View Related
Sep 26, 2011
How do I handle URL connections - such as logging into a web application?
Example 1
When I use .Refresh BackgroundQuery:= False and there is a problem connecting, Excel hangs ... cannot Ctrl-Break ... only option is to kill Excel (and lose changes).
global QT As QueryTable
Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL,
[Code]....
Now, if it's still refreshing, it's ok. But if not, it gets stuck on .Refreshing with run time error 424.
Ideally I want to use .Refresh BackgroundQuery:= False as I am calling a number of urls. I just need to provide an "out" when the url fails and hangs on .Refresh BackgroundQuery:= False
View 6 Replies
View Related
Sep 18, 2013
I have a pivot table that I refresh using .refresh in the VBA code.
My problem is that when I loop through all the PivotItems in a filter list in order to make all visible, there are PivotItem names that aren't in the drop down menu and so the code breaks.
Do I need to refresh the pivot table slightly differently than just using .refresh?
View 1 Replies
View Related
