| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Pivotfilter against Cell value
Hello Experts,
I have today 2 sheets - and I would like to create the following: When the focus is on one cell in sheet 1 - I would like to have a button which the user can click (a macro behind)to insert that value into a pivot filter on sheet 2 - and then refresh the pivottable on sheet 2 based on the new filter How can I do that....? Any help is appriciated Thanks Henrik |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Pivotfilter against Cell value
depending on what you're changing .... you can use the sheet's change event.
example pivot table is on sheet2 and its a sales / marketing pivot. The page field is Region that i want to filter on on sheet1, range A1 is where i place my state code, like MA or OR the code page for sheet1 I add this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("A1").Address Then Sheet2.PivotTables("PivotTable1").PivotFields("reg ion").CurrentPage = Range("A1").Value Sheet2.Activate End If End Sub so when I type a state code into A1 of sheet1, the event fires, the code checks which cell was changed, and if its A1, the value is placed into the page field to change the region filter, then the sheet holding the pivot is activated is this the kind of idea that you want? "Henrik" wrote in message ... Hello Experts, I have today 2 sheets - and I would like to create the following: When the focus is on one cell in sheet 1 - I would like to have a button which the user can click (a macro behind)to insert that value into a pivot filter on sheet 2 - and then refresh the pivottable on sheet 2 based on the new filter How can I do that....? Any help is appriciated Thanks Henrik |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Pivotfilter against Cell value
Thanks for quick response.
Do you have an suggestion to a solution on the following scenarie as well - as after considering the solution rather would go for a solution which can solve the following scenarie: 1)Sheet 1 with Pivottable1 is connected to Sales OLAP db which contains a lot of data including an item numbers 2)When the user stand on one cell in the Item Number column in the pivottable1 - containing the item number - let's say for example "100-200500" - and then click a button 3)Then the Pivottable2 on sheet2 connected to the Production OLAP db get filtered on the item that's selected(cell is active) on sheet1 4) if the item does not exist in the production olap a error message is giving Does it makes sense - and do you have a suggestion for this scenario Thanks again for your help Henrik "Patrick Molloy" wrote: depending on what you're changing .... you can use the sheet's change event. example pivot table is on sheet2 and its a sales / marketing pivot. The page field is Region that i want to filter on on sheet1, range A1 is where i place my state code, like MA or OR the code page for sheet1 I add this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("A1").Address Then Sheet2.PivotTables("PivotTable1").PivotFields("reg ion").CurrentPage = Range("A1").Value Sheet2.Activate End If End Sub so when I type a state code into A1 of sheet1, the event fires, the code checks which cell was changed, and if its A1, the value is placed into the page field to change the region filter, then the sheet holding the pivot is activated is this the kind of idea that you want? "Henrik" wrote in message ... Hello Experts, I have today 2 sheets - and I would like to create the following: When the focus is on one cell in sheet 1 - I would like to have a button which the user can click (a macro behind)to insert that value into a pivot filter on sheet 2 - and then refresh the pivottable on sheet 2 based on the new filter How can I do that....? Any help is appriciated Thanks Henrik |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Pivotfilter against Cell value
it sounds perfectly reasonable
I'd suggest a double click for the cell in pivottable1 your code should checks that the cell thats double clicked is in the correcte column, and then you can push that value to pivottable2 "Henrik" wrote in message ... Thanks for quick response. Do you have an suggestion to a solution on the following scenarie as well - as after considering the solution rather would go for a solution which can solve the following scenarie: 1)Sheet 1 with Pivottable1 is connected to Sales OLAP db which contains a lot of data including an item numbers 2)When the user stand on one cell in the Item Number column in the pivottable1 - containing the item number - let's say for example "100-200500" - and then click a button 3)Then the Pivottable2 on sheet2 connected to the Production OLAP db get filtered on the item that's selected(cell is active) on sheet1 4) if the item does not exist in the production olap a error message is giving Does it makes sense - and do you have a suggestion for this scenario Thanks again for your help Henrik "Patrick Molloy" wrote: depending on what you're changing .... you can use the sheet's change event. example pivot table is on sheet2 and its a sales / marketing pivot. The page field is Region that i want to filter on on sheet1, range A1 is where i place my state code, like MA or OR the code page for sheet1 I add this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("A1").Address Then Sheet2.PivotTables("PivotTable1").PivotFields("reg ion").CurrentPage = Range("A1").Value Sheet2.Activate End If End Sub so when I type a state code into A1 of sheet1, the event fires, the code checks which cell was changed, and if its A1, the value is placed into the page field to change the region filter, then the sheet holding the pivot is activated is this the kind of idea that you want? "Henrik" wrote in message ... Hello Experts, I have today 2 sheets - and I would like to create the following: When the focus is on one cell in sheet 1 - I would like to have a button which the user can click (a macro behind)to insert that value into a pivot filter on sheet 2 - and then refresh the pivottable on sheet 2 based on the new filter How can I do that....? Any help is appriciated Thanks Henrik |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Pivotfilter against Cell value
Do you have a code suggestion how to solve this ?
Thanks Henrik "Patrick Molloy" wrote: it sounds perfectly reasonable I'd suggest a double click for the cell in pivottable1 your code should checks that the cell thats double clicked is in the correcte column, and then you can push that value to pivottable2 "Henrik" wrote in message ... Thanks for quick response. Do you have an suggestion to a solution on the following scenarie as well - as after considering the solution rather would go for a solution which can solve the following scenarie: 1)Sheet 1 with Pivottable1 is connected to Sales OLAP db which contains a lot of data including an item numbers 2)When the user stand on one cell in the Item Number column in the pivottable1 - containing the item number - let's say for example "100-200500" - and then click a button 3)Then the Pivottable2 on sheet2 connected to the Production OLAP db get filtered on the item that's selected(cell is active) on sheet1 4) if the item does not exist in the production olap a error message is giving Does it makes sense - and do you have a suggestion for this scenario Thanks again for your help Henrik "Patrick Molloy" wrote: depending on what you're changing .... you can use the sheet's change event. example pivot table is on sheet2 and its a sales / marketing pivot. The page field is Region that i want to filter on on sheet1, range A1 is where i place my state code, like MA or OR the code page for sheet1 I add this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("A1").Address Then Sheet2.PivotTables("PivotTable1").PivotFields("reg ion").CurrentPage = Range("A1").Value Sheet2.Activate End If End Sub so when I type a state code into A1 of sheet1, the event fires, the code checks which cell was changed, and if its A1, the value is placed into the page field to change the region filter, then the sheet holding the pivot is activated is this the kind of idea that you want? "Henrik" wrote in message ... Hello Experts, I have today 2 sheets - and I would like to create the following: When the focus is on one cell in sheet 1 - I would like to have a button which the user can click (a macro behind)to insert that value into a pivot filter on sheet 2 - and then refresh the pivottable on sheet 2 based on the new filter How can I do that....? Any help is appriciated Thanks Henrik |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't change pivotfilter value1 parameter in code | Excel Programming | |||
| Data Validation lists update orginal cell with list update | Excel Worksheet Functions | |||
| auto-hide rows, cell format (# and @), update cell refs, shade cel | Excel Discussion (Misc queries) | |||
| Populating Last Saved Date in Cell AND also update that same cell in Header | Excel Discussion (Misc queries) | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
