+ Reply to Thread
Results 1 to 9 of 9

Pivot table cope with inserted columns

  1. #1
    Registered User
    Join Date
    09-27-2006
    Posts
    9

    Pivot table cope with inserted columns

    Hello,

    We are using a named range so our pivot tables can cope with new rows but is there any way to create a pivot table that uses named ranges for each column so that inserted columns etc don't break the pivot table? Our data sheets are already named like that (one large named range then named ranged per column)...

    The motivation behind this is that our users are allowed to add their own columns to the underlying data and we would really like to stick with Excel for the analysis.

    Many thanks

    Sam

  2. #2
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    "so that inserted columns etc don't break the pivot table"

    Can you explain why the inserted columns break your pivot table?

    I always name the pivot table data range as something like 'rawdata'. If I insert a column nothing breaks.
    regards

  3. #3
    Registered User
    Join Date
    09-27-2006
    Posts
    9

    upload

    I presume it is because the data gets upload in one new bulk rather (via a macro that fetches the data from a website). The macro then adds the correct named ranges back (using the column headings) but the pivot table now points at the wrong columns within the "everything" named range.

    Presumably if I inserted the columns into the sheet that the pivot table is based on the pivot table would not break. That might be possible but would be a big change...

  4. #4
    Registered User
    Join Date
    09-27-2006
    Posts
    9
    Thanks, I think that has pointed me in the right direction. Planning to point the web query at a new sheet then "copy" that data into the original data range or insert new columns. Presumably that will mean that the pivot table references will remain valid. I'm very vague on exactly how the pivot table holds its references but hopefully something like freshDataColumnRange.Copy(originalDataColumnRange) will work!?


    Quote Originally Posted by steven1001
    "so that inserted columns etc don't break the pivot table"

    Can you explain why the inserted columns break your pivot table?

    I always name the pivot table data range as something like 'rawdata'. If I insert a column nothing breaks.
    regards

  5. #5
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    Check for the functionality that allows you to have extra columns with formulas adjacent to the data returned by your query. In the query properties there is an 'auto copy down adjacent formulas. It also expands the named range to include the new rows automatically.

  6. #6
    Registered User
    Join Date
    09-27-2006
    Posts
    9

    Uses column title as key!?

    Thanks again Steven,

    After some experimentation the biggest shock was finding that the pivot table uses the column headings as the "key". So presumably it just holds strings for the datasource (in my case a worksheet), the range expression and each column title...

    Nice and simple but a bit fragile if a user notices that a heading had a typo etc.

    Anyway, will take me a while to get my head around it.

    Named ranges for each column seem completely useless for pivot tables.

    Cheers

    Sam

  7. #7
    Registered User
    Join Date
    09-27-2006
    Posts
    9

    .FillAdjacentFormulas

    Read the online MS documentation but none the wiser. Can anybody recommend a Excel VBA reference book? I'm a Java server side developer so I know the theory but it is a very different environment.

    Many thanks

    Sam

  8. #8
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    pls see attached
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-27-2006
    Posts
    9

    Clearer

    Thanks Stephen,

    All a lot clearer now. Just need to tidy up some existing pivot tables then think it should all work nicely.

    Cheers

    Sam

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1