Pivot tables and dynamic columns

[excelboy]

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]

You could use a dynamic range as the pivot table source. There are
instructions here:

http://www.contextures.com/xlPivot01.html

 

[excelboy]

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]

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.

 

[Debra Dalgleish]

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?