1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Pivot table with data validation

Discussion in 'Business Applications' started by vinwin06, Dec 3, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    HI,

    I required vba codings for pivot table but the value should be choose from the data validation i have created.

    For example : I have two pivot tables in sheet1 and these two pivot table have a common column filed available so i have created the data validation in cell D6. Now i need to give reference to the cell D6 for both the pivot table on the column labels.

    Based on the value selected on the drop down the pivot table need to be changed on the both pivot tables.

    Regards,

    VInwin
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I'm sorry but I think a sample will clarify what you need and how your data is built.
    Don't forget to mention which Excel version you're using.
     
  3. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    Hi,

    Find attached the sample file i have made the pivot in sheet 4. Based on the value selected in cell E2 the pivot values in C4 and T4 needs to be changed.I am using excel 2007 version.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Got the file, will take a look after work
     
  5. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    VInwin, see the attached workbook. It's a .xlsm file, so I hope you are okay with some code. I placed the following code in the worksheet for the pivot table that will update your table when you change the selection in cell E2...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.ScreenUpdating = False
    
    If Target.Address = "$E$2" Then
    
        Dim pi As PivotItem
        Dim pt As PivotTable
        
        For Each pt In ActiveSheet.PivotTables
            'sets all fields to visible, can just check select all, but i forgot how to do that!
            For Each pi In pt.PivotFields("COB").PivotItems
                pi.Visible = True
            Next
            
            For Each pi In pt.PivotFields("COB").PivotItems
                If pi.Name = Target Then pi.Visible = True Else pi.Visible = False
            Next
        Next
    
    End If
    
    Application.ScreenUpdating = False
    
    End Sub
    
    Hans, sorry to step on your work solution, but this looked pretty straight forward to me. L
     

    Attached Files:

  6. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    Thanks Scotty. Its working for me , but i have a problem that user can also change the values in pivot table also. So it will make some data if they any body selected the filter in pivot table instead of data validation cell.

    Can we do that in any way....
     
  7. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Can you please explain what you mean again. It wasn't very clear.

    Specifically this sentence: "So it will make some data if they any body selected the filter in pivot table instead of data validation cell"

    Can you re-state and/or elaborate?
     
  8. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Or if it's just a matter of you that you won't to force the user to use the drop-down list and not be able to use the pivot table drop-downs, you can lock the cells and protect the sheet - not allowing them to manipulate pivot tables.
     
  9. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    Ya Scotty , i have tried to protect the cell but after that i cannot able to change the data validation filter also.
     
  10. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Here's what you would need to do - at least with this request in a vacuum (not considering other things that may need to get done in the same sheet):

    1. Unlock all the cells
    2. Lock the two cells where you can change the pivot fields.
    3. Protect the sheet (at the very least no allowing the selection of locked cells and the manipulation of pivot reports
     
  11. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    Thank you so much Scotty. One more help from you if i want to add another data validation filter above this filter means what kind of changes i required to do so.

    I have attached the sample file now i want to create a data validation filter for ERP column in pivot table.

    I have tried based on ur code but i cannot get the result expected.
     

    Attached Files:

  12. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Here you go. All set. I had some extra time so I cleaned up the code a a bit and made it more efficient. I also commented it pretty good so that I could help you learn VBA a bit, hopefully. I don't know.

    Also, I did name the two ranges where your validate lists are so that you can move them around or insert, delete columns / rows without worrying about the code getting screwed up.

    Any questions, please reach out...
     

    Attached Files:

  13. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    Thank you Scotty. I already know about something about VBA and i can work on VBA some of the basic codes. This kind of pivot in VBA im learning. anyway thanks for ur help.........
     
  14. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
  15. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    Once again thank you Scotty. Can you suggest me some of the books or website to get expertise in VBA.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029585

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice