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. What’s 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 Excel’s 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 |
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. What’s 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 Excel’s column letter? Thanks. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
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 it’s automatically added to my pivot tables. I’m not sure how this will help me with the shifting columns problem? Maybe I wasn’t very clear on what I’m trying to do. Here’s 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. I’m 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 |
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 it’s automatically added to my pivot tables. I’m not sure how this will help me with the shifting columns problem? Maybe I wasn’t very clear on what I’m trying to do. Here’s an example. Let’s 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. I’m 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 |
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 it’s automatically added to my pivot tables. I’m not sure how this will help me with the shifting columns problem? Maybe I wasn’t very clear on what I’m trying to do. Here’s 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. I’m 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 |
All times are GMT +1. The time now is 05:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com