Solved: Pivot table with data validation

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 

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.
 

Attachments

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
 

Attachments

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....
 
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?
 
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.
 

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.
 
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
 

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.
 

Attachments

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...
 

Attachments

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.........
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top