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?action=getinfo&userid=37547
View this thread: http://www.excelforum.com/showthread.php?threadid=571818

0
8/15/2006 3:52:48 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
408 Views

Similar Articles

[PageSpeed] 19

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

   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

0
dsd1 (5911)
8/15/2006 11:13:50 PM
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?action=getinfo&userid=37547
View this thread: http://www.excelforum.com/showthread.php?threadid=571818

0
8/16/2006 3:26:21 PM
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?action=getinfo&userid=37547
View this thread: http://www.excelforum.com/showthread.php?threadid=571818

0
8/16/2006 3:33:30 PM
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

0
dsd1 (5911)
8/17/2006 2:23:25 AM
Reply:

Similar Artilces:

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

format of excel file to be counted as Table for Access
While importing external data, there is an option of importing an excel file in the form of a Table. Though a table in Access also has headings & data below it, what does the utility expect an excel file to contain? Thanks ...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

Null or NOT in another table
Hello. I have a query that verifies that required fields in my records are populated and correct. Right now it returns the records that have required fields=NULL. some of the fields need to be verified for legal values from another table as well. So I need to return values where the value of field [Tag]! [function_code] is not in [WM_GOC_Conversion]![Function_Code] My current query is a datasheet with autoformat that marks the null- fields with yellow background. I'd like to be able to mark the wrong information with red text, but first I need to have a query that return the...

Embedding Word table in Excel cell
Is there any way in which I can embed a small MS Word table in an Excel cell? Thanks. -- Larry Lester Copy / paste as a picture. Pictures can always be re-sized and re-positioned -- Gary's Student "Larry Lester" wrote: > Is there any way in which I can embed a small MS Word table in an Excel cell? > Thanks. > -- > Larry Lester Gary's reply works for pasting the table in as a graphic object _on_ an XL sheet, but you cannot paste a graphic into a cell as cell content. If you simply Paste to a cell, the corresponding number of cells will be filled with th...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

updating and underlying table field with a combo box selection
I want to use an ID value from a combo box on a subform to update the same ID value in the form's underlying table. My main form is: frm_Street_Joiner_Main My Subform is: frm_Street_Joiner_Sub My Subform table is: tbl_Street_Joiner My Mainform is: frm_Street_Joiner_Main The combo box on my subform is called: StreetName, with a column count of two but the bound column is the actual street name. SELECT QRY_Street_Names_Joiner_Master.Street_Names, QRY_Street_Names_Joiner_Master.StreetNameID FROM QRY_Street_Names_Joiner_Master ORDER BY QRY_Street_Names_Joiner_Master.Street_Names, QR...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

MatrixAttributeDisplayOrder table cleanup
With the migration from 1.3 to 2.0 the MatrixAttributeDisplayOrder table was created for all matrixs. It looks like many extra entries were created in the conversion process. It appears that the conversion issue has been resolved with SP1. How can I cleanup this table with out deleating and redefining all these matrixs? Currently some of these matrixes take 30 to 45 min. to open up under manager do to the extra entries. Thanks TomT ...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

Columns in Money 2004
Is there any way to change the default columns in the register view ? I really dont want the flagged column, and would like to include a column for categories. Is it possible to do this ? In microsoft.public.money, VerticalCobra wrote: >Is there any way to change the default columns in the register view ? I >really dont want the flagged column, and would like to include a column for >categories. Is it possible to do this ? No. Try Ctrl+T to toggle to multi-line display and back. ...

How to generate dynamic XML from aspx page
Dear Friends, I was using the below code in ASP to dynamically generate XML from a SQL Query Does anyone knows how can I migrate this code to VB and ASPX? Regards Robson Machado Response.ContentType = "text/xml" Response.Write "<?xml version='1.0' ?>" dim RS, CN set CN = server.CreateObject("adodb.connection") set RS = server.CreateObject("adodb.recordset") CN.ConnectionString = strConnect CN.Open RS.Open SQLStmt,cn Response.Write "<ROOT>" Response.Write RS2XML(RS,"RESULT") Response.Write "</R...

Retrieving the Top 10 of the Average of Two columns, but displaying a third
I'm new to Excel programing, so please bear with me. I have an excel spreadsheet with 3 columns. Column1 Column2 Column3 -------------------------------------- John Doe 5 3 Jane Smith 2 9 Bill Smith 4 2 What I need to do is take the average of Column 2 and 3, find the highest average of the entire list and display Column 1 as the final result. In other words: I need to create a Top 5 List of the people with the highest grade average. I don't care much for displaying the grade, I just need the top 5...

"Dynamic message" on a report
I have been trying to figure out a way to do this and have not been able to find anything concrete to help me. I have a report containing several sub-reports. It is an inventory report for a variety of production lines. If the inventory on a certain die set is less then 0.5, I want to be able to display a message on the report telling the user that the inventory on this die set is low drawing attention to it. Can anyone provide an example of how I can accomplish this? I was thinking of an If, Then, Else If but have never seen it for a report. Any assistance would be appreciated. Thank yo...

how do i "de-dupe?" rows by a column value?
I have a complex operation to perform. My spreadsheet has rows that indicate items (control number in column A) linked to titles (control number in column B). A title can have multiple items in the spredsheet. I want to number the titles and assign random numbers to them so that I can get two random samples. I also want to have my random numbering retained in the spreadsheet as a whole, so I can see all the items assigned to my samples. How can I "dedupe" column B, assign a random number to each title control, then transfer that data to my "undeduped" spreadshe...

Form to table data
Firstly I'd like to thank all of those helping guys like me. Thanks a million, you're helping more than you think. This is the scenario. I have three tables which were automatically turned into forms. Table 1 = ChanceryTable Table 2 = ProtocolTable Table 3 = CoupleTable I start out at a form (Protocol Form) that works from the "Protocol Table". When I go to the Couple's Form (working off the CoupleTable) from the Protocol Form I'd like for the newly created "Protocol Number" to be consistent with the new couple info that is going to be added to th...

Dynamic Bar Chart Category Axis Problem
Hi I have created a dynamic bar chart, roughly based on Stephen Bullen's funchrt4. The number of categories can range from 2 to about 50 and ideally I would like the category axis names font to be Arial 10 at least. When the number of categories gets towards 50, the category axis names disappear altogether and the only way I have found to retrieve them is to manually reduce the font size to about 7 (dependent on the level of the zoom). This font size is excessively small for the majority of charts. Is there any way to get Excel2002 to keep the category axis names in all circumstan...

A Crystal Beginner
I am using Crystal 9.2.4.77 and have loaded the Crystal enhancements. Now I want to do against the CRM database. What do I do? I thought I would create a data source to the CRM SQLserver, which I did, then connect to it through Crystal Reports. That works, but when I drill down on the AAA_MSCRM database to choose my tables, it shows me dbo as the only option (where did this come from??) and under that are just a few generic tables. No CRM tables. Something's not quite right. What am I missing? file - log on to aps server "KBLawson" <klawson@aetinc.com> wrote in ...