+ Reply to Thread
Results 1 to 7 of 7

[SOLVED] Drop down control pivot table but NOT overwrite value if not found

  1. #1
    Forum Contributor
    Join Date
    04-14-2008
    Location
    Fort Collins, Co
    MS-Off Ver
    2016
    Posts
    107

    [SOLVED] Drop down control pivot table but NOT overwrite value if not found

    I have a drop down that, when an item is selected, it selects that same item in the pivot table filter

    However, sometimes, that item is not there for a particular month.

    I need to locate the item in the pivot table filter and if it's not there, I don't want it to overwrite the name of the item in the pivot, I want it to display a message 'Selection not found' and end the subroutine...

    Here is a sample of the code I am using:
    Please Login or Register  to view this content.
    EXAMPLE ISSUE:

    My pivot filter has the following in the list:

    A, B, C, D

    My drop down has A,B,C,D

    The following month, my pivot only has A,B,C
    If someone selects D in my drop down, it OVERWRITES the most recent item selected in the pivot filter, renaming it 'D'
    Last edited by 4am; 02-01-2012 at 05:05 PM.
    Is That Your Spreadsheet Or Did Your Database Just Throw Up?


  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,687

    Re: Drop down control pivot table but NOT overwrite value if value not found

    4am,

    See if the code below and in the attachment gets you closer to your goal.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    04-14-2008
    Location
    Fort Collins, Co
    MS-Off Ver
    2016
    Posts
    107

    Re: Drop down control pivot table but NOT overwrite value if value not found

    Thank you. I will go try this code!

    EDIT: THIS WORKED PERFECTLY! Thank you so much!

    EDIT: Need a little more help, see next post!
    Last edited by 4am; 02-01-2012 at 04:21 PM.

  4. #4
    Forum Contributor
    Join Date
    04-14-2008
    Location
    Fort Collins, Co
    MS-Off Ver
    2016
    Posts
    107

    Re: Drop down control pivot table but NOT overwrite value if value not found

    Had marked this as 'solved', but I want to know how to modify the above code to update 2 additional pivot tables with the same drop down selection value, one on the active sheet and one on another sheet called 'sheet 1' (for example)

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,687

    Re: Drop down control pivot table but NOT overwrite value if value not found

    Hmm, there's a few things we can do. This one will do it for all Pivot tables in the workbook:
    Please Login or Register  to view this content.
    This one will update the Pivots listed in the array
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    04-14-2008
    Location
    Fort Collins, Co
    MS-Off Ver
    2016
    Posts
    107

    Re: [NEED MORE HELP] Drop down control pivot table but NOT overwrite value if not fo

    Thank you. This will be helpful for most of my code!
    Last edited by 4am; 02-01-2012 at 05:04 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,687

    Re: [SOLVED] Drop down control pivot table but NOT overwrite value if not found

    4am, I saw that you edited out part of your last reply:
    Please Login or Register  to view this content.
    I didn't know if you found a solution, so wanted to provide a little bit of help just in case. The code below will give you the name of each PivotTable in your activesheet. Armed with their names, you can alter either the first macro by changing the pivot table name, or in the last ones you could alter them to update only a specific PT, or all BUT a specific PT.
    Please Login or Register  to view this content.

+ 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