Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot tables and dynamic columns
I am using pivot tables and pivot reports to analyze survey data imported from our customer survey program into Excel. This works well but the problem is we periodically add new questions to the survey, which then causes the columns to shift and messes up our pivot tables. Whats the best way to deal with the shifting columns, so that we do not have to redo the pivot tables every time we make a change to our survey? Is there a way to base the pivot table data range on the column header name, rather than Excels column letter? Thanks. -- excelboy ------------------------------------------------------------------------ excelboy's Profile: http://www.excelforum.com/member.php...o&userid=37547 View this thread: http://www.excelforum.com/showthread...hreadid=571818 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot tables and dynamic columns
You could use a dynamic range as the pivot table source. There are
instructions he http://www.contextures.com/xlPivot01.html excelboy wrote: I am using pivot tables and pivot reports to analyze survey data imported from our customer survey program into Excel. This works well but the problem is we periodically add new questions to the survey, which then causes the columns to shift and messes up our pivot tables. Whats the best way to deal with the shifting columns, so that we do not have to redo the pivot tables every time we make a change to our survey? Is there a way to base the pivot table data range on the column header name, rather than Excels column letter? Thanks. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot tables and dynamic columns
I'm already using that method to make the pivot tables update properly when new information is added to my spreadsheet. For example when I add survey data from a new month its automatically added to my pivot tables. Im not sure how this will help me with the shifting columns problem? Maybe I wasnt very clear on what Im trying to do. Heres an illustration: A B C Month Color Units June Blue 45 June Red 21 June Blue 33 A B C D Month Color Name Units June Blue 45 June Red 21 June Blue 33 July Blue Ted 45 July Red Bill 65 The problem is when we add new questions to our survey (such as the Name example above) the columns shift when we export them into excel and our pivot tables mess up. Im just wondering what the best way is to deal with this? Maybe a macro that automatically rearranges the columns? There are a lot of columns, so doing it manually is a hassle. -- excelboy ------------------------------------------------------------------------ excelboy's Profile: http://www.excelforum.com/member.php...o&userid=37547 View this thread: http://www.excelforum.com/showthread...hreadid=571818 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot tables and dynamic columns
I'm already using that method to make the pivot tables update properly when new information is added to my spreadsheet. For example when I add survey data from a new month its automatically added to my pivot tables. Im not sure how this will help me with the shifting columns problem? Maybe I wasnt very clear on what Im trying to do. Heres an example. Lets say column C contains the names of people we've surveyed -- if we add a new question to our survey column C might shift to column D when exported into excel. Column C would then be replaced with the results from the new question. This breaks our pivot tables. Im just wondering what the best way is to deal with this? Maybe a macro that automatically rearranges the columns? There are a lot of columns, so doing it manually is a hassle. -- excelboy ------------------------------------------------------------------------ excelboy's Profile: http://www.excelforum.com/member.php...o&userid=37547 View this thread: http://www.excelforum.com/showthread...hreadid=571818 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot tables and dynamic columns
If you insert new columns in the source data, and include all the
columns in the source range, the pivot table should retain the old headings, and add any new ones. What happens in your pivot table? Do fields disappear? excelboy wrote: I'm already using that method to make the pivot tables update properly when new information is added to my spreadsheet. For example when I add survey data from a new month its automatically added to my pivot tables. Im not sure how this will help me with the shifting columns problem? Maybe I wasnt very clear on what Im trying to do. Heres an illustration: A B C Month Color Units June Blue 45 June Red 21 June Blue 33 A B C D Month Color Name Units June Blue 45 June Red 21 June Blue 33 July Blue Ted 45 July Red Bill 65 The problem is when we add new questions to our survey (such as the Name example above) the columns shift when we export them into excel and our pivot tables mess up. Im just wondering what the best way is to deal with this? Maybe a macro that automatically rearranges the columns? There are a lot of columns, so doing it manually is a hassle. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot tables using dynamic data source | Excel Discussion (Misc queries) | |||
overlapping pivot tables | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Dynamic Pivot tables | Excel Discussion (Misc queries) |