• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing the values of a pivot chart using a combo box

A

ashl

Member
Hi,


I'm trying to make an interactive dashboard with my basic and limited excel skills(eek).


I've created a pivot table linked to my data and created a pivot chart. Using the filters on the pivot table I can filter the data I want to see on the chart. I was wondering if it is possible to do this on the pivot chart using a combo box? oh and without writing a complex code?


The reason being I want my user to be able to filter the pivot chart without using the pivot table. I want to use the pivot chart and not a regular chart so that it is easily updated.


So far Ive been able to add the combo box to the chart and manage to refernce what I want to filter but when I select it the data in the chart doesn't change.


Any help would be greatly appreciated, its driving me a bit crazy :)


Thanks :)
 
first thing,

where ever your "report filter" filter is located, select the cell and give it a name. For example, cell B1 is my filter button on the pivot, i select B1 and type in the left field of the formula bar OperationSel


then add your combo box.

right click the combo box and select format control and select the control tab. then add your range that you want to combo box to show. then put a cell link in a nearby cell. for example in cell C1, in D1 you are going to use an offset formula to reference your link from your combo box. type =offset(sheet1!G1:G63,sheet2!C1-1,) then click ctrl + shift + enter. the sheet1G1:G63 is your combo box reference, C1 is your link from your combo box. now when you click on the combo box, the value in the combo box should be exactly what your value in D1 is. then give the cell D1 a name like you did for the pivot. so click on D1 and go the the field on the left of your formula bar and type Sel or something.


VB code


Sub RunMacro()

[OperationSel] = [Sel]

end sub.


that is it.


must put the square backets to identify a named range.


Greg
 
Hi Greg

A big thank u for your quick and awesome reply!


Just a quick (dumb) question why do u need to click control and shift and enter? When I do it it just opens a folder to save the spreadsheet.


Thanks :)
 
control and shift and enter puts the formula as an array formula, when you use offset, typically an array is required, in this case, if you don't have an array {} those brackets around your formula, i don't think it will work properly.


i don't know why you get the save folder, the only thing i can think of is you might have hit Crtl + S, which is the short cut for save.


how was the instruction? Was it easy to follow? any suggestions to improve from how you did it to how i explained it?


Thanks
 
Hi Greg,


I'm having problems getting the formula to work would u be able to put a link to an example up pretty plz so i can see if I'm refencing the right cells and why the formula doesn't work. I'm on excel 2007 im not sure if that has something to do with it?


Thanks :)
 
unfortunately, I am unable to post. i think the problem still lies in the array formula. double click the cell where the formula is, then select ctrl + shift+ enter and it should work.

http://chandoo.org/wp/2012/07/03/find-the-last-date-of-an-activity/

this may help in explaining the array formula.


please provide your formula for me to look at.
 
Hi Greg,


I've got the formula working and I ran the macros.


But all it does is change the filter and not the values in the chart
 
Wireless industrial remotes are also used to control material handling equipment such as hoists,Isabel Marant Sneakers Bekket High-top Suede Black,Remote control technology is used in many applications as well as engineering industries Mark Allen Smith has hit a home run with his first novel, When he arrives.
Such devices require a processing unit. This includes Low, "We had the best time relaxing,Isabel Marant Sneakers Bekket High-top Suede Beige White, white pleated mini skirt and sky-high Christian Louboutin heels, films like Star Wars and Star Trek have improved the overall image of a Geek without realizing it. However,isabel marant shoes, this mobile as easy as pie attracted the young and adult generations alike. All relating to many of these attributions can be the case applicable for more information on this mobile,isabel marant, Christina Aguilera is surprisingly motherly (she raised an eyebrow over the hip grinding from 17-year-old RaeLynn).

Related articles：

 
Thanks Greg but I used the formula on a normal chart without the macros and linked it to a combo box and it does what I want :) Sorry for the late reply but thanks for the formula :)
 
You must log in or register to reply here.
Back
Top