Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Hi,
I am trying to solve the following issue: My excel workbook includes two kind of input values: 1) Assumptions (I am using a Scenario PivotTable Report to track the effect of different assumption sets on the "result cell"); 2) Background data (Its values also affect the "result cell" and they are changing time to time. I can not create scenarios for every background data cell, as there are too many of them). Every time background data change (quite often), I must rebuild Scenario PivotTable Report, as it does not take into account the updated values. Is there any quick way to refresh a Scenario PivotTable Report? Thanks in advance, Gerry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
I forgot to mention - I am using MS Excel 2003
"Gerry" wrote: Hi, I am trying to solve the following issue: My excel workbook includes two kind of input values: 1) Assumptions (I am using a Scenario PivotTable Report to track the effect of different assumption sets on the "result cell"); 2) Background data (Its values also affect the "result cell" and they are changing time to time. I can not create scenarios for every background data cell, as there are too many of them). Every time background data change (quite often), I must rebuild Scenario PivotTable Report, as it does not take into account the updated values. Is there any quick way to refresh a Scenario PivotTable Report? Thanks in advance, Gerry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Unfortunately, there's no way to refresh a Scenario Pivot Table.
If the information changes, you can create a new copy of the pivot table. Gerry wrote: Hi, I am trying to solve the following issue: My excel workbook includes two kind of input values: 1) Assumptions (I am using a Scenario PivotTable Report to track the effect of different assumption sets on the "result cell"); 2) Background data (Its values also affect the "result cell" and they are changing time to time. I can not create scenarios for every background data cell, as there are too many of them). Every time background data change (quite often), I must rebuild Scenario PivotTable Report, as it does not take into account the updated values. Is there any quick way to refresh a Scenario PivotTable Report? Thanks in advance, Gerry -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
You could also look at the Scenario Alternative sample file he
http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' Gerry wrote: Hi, I am trying to solve the following issue: My excel workbook includes two kind of input values: 1) Assumptions (I am using a Scenario PivotTable Report to track the effect of different assumption sets on the "result cell"); 2) Background data (Its values also affect the "result cell" and they are changing time to time. I can not create scenarios for every background data cell, as there are too many of them). Every time background data change (quite often), I must rebuild Scenario PivotTable Report, as it does not take into account the updated values. Is there any quick way to refresh a Scenario PivotTable Report? Thanks in advance, Gerry -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Thanks.
"Debra Dalgleish" wrote: Unfortunately, there's no way to refresh a Scenario Pivot Table. If the information changes, you can create a new copy of the pivot table. Gerry wrote: Hi, I am trying to solve the following issue: My excel workbook includes two kind of input values: 1) Assumptions (I am using a Scenario PivotTable Report to track the effect of different assumption sets on the "result cell"); 2) Background data (Its values also affect the "result cell" and they are changing time to time. I can not create scenarios for every background data cell, as there are too many of them). Every time background data change (quite often), I must rebuild Scenario PivotTable Report, as it does not take into account the updated values. Is there any quick way to refresh a Scenario PivotTable Report? Thanks in advance, Gerry -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Thanks again.
I have also tried creating macro command that would build a new scenario pivot report. I have recorded macro command by manually creating and formating the Scenario Pivot Table Report. In result, the macro command managed to hide fields i did not need, but it failed to assign new data to pivot fields (for example, to move certain data from the column field to the row field). How could I fix this? If that is possible, it could be the solution for the problem. "Debra Dalgleish" wrote: You could also look at the Scenario Alternative sample file he http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Did you get error messages?
What does the code look like, and what part of the code isn't working as you expect? Gerry wrote: Thanks again. I have also tried creating macro command that would build a new scenario pivot report. I have recorded macro command by manually creating and formating the Scenario Pivot Table Report. In result, the macro command managed to hide fields i did not need, but it failed to assign new data to pivot fields (for example, to move certain data from the column field to the row field). How could I fix this? If that is possible, it could be the solution for the problem. "Debra Dalgleish" wrote: You could also look at the Scenario Alternative sample file he http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
I have figured out what was wrong - whenever you create a new pivot report,
automatically generated name is assigned to it (pivotreport1, pivotreport2, ect.). My Macro referred to the particular pivot report name that was generated at the moment of recording. As I used the same macro to generate another pivot report, the newly assigned name did not math the name in my macro command. The sequence of automatically generated pivot report names is restarted when you reopen the file (again starts from pivotreport1). The macro must refer to "pivotreport1" - then it will work once after you reopen the file. The sample code: ActiveSheet.Scenarios.CreateSummary ReportType:=xlPivotTable, ResultCells:= _ Range("F19") Range("A8").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Price" _ ) .Orientation = xlPageField .Position = 1 End With Even this is not ideal solution, its far more than nothing. Could you suggest the code that would allow rebuilding a scenario pivot report without reopening a file each time? "Debra Dalgleish" wrote: Did you get error messages? What does the code look like, and what part of the code isn't working as you expect? Gerry wrote: Thanks again. I have also tried creating macro command that would build a new scenario pivot report. I have recorded macro command by manually creating and formating the Scenario Pivot Table Report. In result, the macro command managed to hide fields i did not need, but it failed to assign new data to pivot fields (for example, to move certain data from the column field to the row field). How could I fix this? If that is possible, it could be the solution for the problem. "Debra Dalgleish" wrote: You could also look at the Scenario Alternative sample file he http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Refer to the pivot table by number, and you should avoid the problem:
With ActiveSheet.PivotTables(1).PivotFields("Price") Gerry wrote: I have figured out what was wrong - whenever you create a new pivot report, automatically generated name is assigned to it (pivotreport1, pivotreport2, ect.). My Macro referred to the particular pivot report name that was generated at the moment of recording. As I used the same macro to generate another pivot report, the newly assigned name did not math the name in my macro command. The sequence of automatically generated pivot report names is restarted when you reopen the file (again starts from pivotreport1). The macro must refer to "pivotreport1" - then it will work once after you reopen the file. The sample code: ActiveSheet.Scenarios.CreateSummary ReportType:=xlPivotTable, ResultCells:= _ Range("F19") Range("A8").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Price" _ ) .Orientation = xlPageField .Position = 1 End With Even this is not ideal solution, its far more than nothing. Could you suggest the code that would allow rebuilding a scenario pivot report without reopening a file each time? "Debra Dalgleish" wrote: Did you get error messages? What does the code look like, and what part of the code isn't working as you expect? Gerry wrote: Thanks again. I have also tried creating macro command that would build a new scenario pivot report. I have recorded macro command by manually creating and formating the Scenario Pivot Table Report. In result, the macro command managed to hide fields i did not need, but it failed to assign new data to pivot fields (for example, to move certain data from the column field to the row field). How could I fix this? If that is possible, it could be the solution for the problem. "Debra Dalgleish" wrote: You could also look at the Scenario Alternative sample file he http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Worked out perfectly. Thank you in deed!
"Debra Dalgleish" wrote: Refer to the pivot table by number, and you should avoid the problem: With ActiveSheet.PivotTables(1).PivotFields("Price") Gerry wrote: I have figured out what was wrong - whenever you create a new pivot report, automatically generated name is assigned to it (pivotreport1, pivotreport2, ect.). My Macro referred to the particular pivot report name that was generated at the moment of recording. As I used the same macro to generate another pivot report, the newly assigned name did not math the name in my macro command. The sequence of automatically generated pivot report names is restarted when you reopen the file (again starts from pivotreport1). The macro must refer to "pivotreport1" - then it will work once after you reopen the file. The sample code: ActiveSheet.Scenarios.CreateSummary ReportType:=xlPivotTable, ResultCells:= _ Range("F19") Range("A8").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Price" _ ) .Orientation = xlPageField .Position = 1 End With Even this is not ideal solution, its far more than nothing. Could you suggest the code that would allow rebuilding a scenario pivot report without reopening a file each time? "Debra Dalgleish" wrote: Did you get error messages? What does the code look like, and what part of the code isn't working as you expect? Gerry wrote: Thanks again. I have also tried creating macro command that would build a new scenario pivot report. I have recorded macro command by manually creating and formating the Scenario Pivot Table Report. In result, the macro command managed to hide fields i did not need, but it failed to assign new data to pivot fields (for example, to move certain data from the column field to the row field). How could I fix this? If that is possible, it could be the solution for the problem. "Debra Dalgleish" wrote: You could also look at the Scenario Alternative sample file he http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh and PivotTable with Analysis Services | Excel Worksheet Functions | |||
Excel 2k Pivot Table refresh scenario | Excel Discussion (Misc queries) | |||
Refresh scenario information in a pivot table | Excel Discussion (Misc queries) | |||
Refresh Pivottable last 4 weeks data from today | Excel Worksheet Functions | |||
why report manager ignores the range set in the scenario | Excel Discussion (Misc queries) |