+ Reply to Thread
Results 1 to 31 of 31

Pivot table data selection

  1. #1
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Lightbulb Pivot table data selection

    Hello my friends
    I have a problem...
    How can I create a search form in order to select a record from a pivot table, instead of selecting from the filter drop down menu? I need to select only one record at a time(one customer) from a pivot table and I dont want to do: pivot filter...select all.. unselect... type fast the customer id.. and then select specific customer.....
    Thank you in advance for your help..

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    You can create a drop down menu in a cell using Data Validation... then based on the selection you can use VLOOKUP to get the remainder of the data from the Pivot table columns....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    I have attached the excel (sorry for the Greek) in order to show you better what I need to do..
    Instead of selecting from the pivot table with mouse in need a search form ( I dont want to use mouse...).
    If this is not possible, how can I use the keyboard to select/deselect from the drop down menu?
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    I think that will require VBA (i.e. a macro)... which will require a VBA expert to help you... please wait for someone with that expertise to assist.

  5. #5
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    ok thank you anyway..

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot table data selection

    ALT+DOWN_ARROW will display the filter selection dialog.
    You can use the SPACE bar to toggle selections
    Cheers
    Andy
    www.andypope.info

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    That's interesting.. thanks Andy.

    Although, in 2007, at least, I think you need to TAB through first to get to the actual selection, then you need to use a combination of the SPACE bar and DOWN_ARROW to toggle individual selections to filter for....

  8. #8
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    thank you but I need something more easy to be used by unfamiliar to excel people.
    So if I tell them press alt and down arrow and then tab and then whatever... they are going to look at me like I m an alien..... I need a seach form to be built instead of the filter button on pivot. Something where when a user writes for example customer id, he will get the specific customer in the field.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    Have a look at the attached in the ΠΕΛΑΤΕΣ sheet, which is the source for the Pivot Table.

    in G1 there is a data validation. So you can pick ID from the drop down list.. then in H1 the associated column B value will appear. Is that what you want?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    I actually need in sheet ΦΟΡΜΑ ΕΙΣΑΓΩΓΗΣ where there is a drop down filter menu of the pivot table, instead of selecting the specific id from the drop down menu, I need to just enter the id value in a field and then the filter to work.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    ... back to VBA....

  12. #12
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    lol lol lol.... VBA rules....

  13. #13
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    but I need someone to know vba fast... I offer free vacations to Greece

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    If you google for "filter pivot tables with Vba" you should come up with some hits that may help you understand what coding you should use.

    Here is one small tutorial...http://www.ehow.com/how_7592699_chan...ilter-vba.html

  15. #15
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    dead end......

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    Try something like this assigned to a button:

    Please Login or Register  to view this content.
    where Range("G1").Value is the cell containing the filter value desired.

    Also please replace the "aaaaaaaaa" with the title of column in the Pivot Table, ΠΕΛΑΤΗΣ... my editor isn't recognizing it..

    Hopefully that helps.

  17. #17
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    Thanx for the code but It has a problem in line:

    .PivotItems(xVar).Visible = True

    error: Unable to get the PivotItems property of the PivotField class

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    Are you entering a filter value in G1 before you run the macro?

  19. #19
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Pivot table data selection

    Quote Originally Posted by kolovel View Post
    thank you but I need something more easy to be used by unfamiliar to excel people.
    So if I tell them press alt and down arrow and then tab and then whatever... they are going to look at me like I m an alien..... I need a seach form to be built instead of the filter button on pivot. Something where when a user writes for example customer id, he will get the specific customer in the field.
    I recommend that you encourage the people to become more accustomed to excel, it may only take a small investment of your time ie 3 x 10 minutes & they will have new skills, plus then you will not need to spend extra time coding extra workarounds when there is existing functionality in excel.

    Just so you know, I am a keyboard person (on the basis of speed), so I try to avoid the mouse wherever possible, but it is important for us/me to understand why this avoidance is necessary.
    Why don't you want to use the mouse?
    Do all your users feel the same way, or are there no mice at their computers?

    In addition to the already suggested options...
    - change the "activesheet" prefix in the code to explicitly state the sheet name eg
    Please Login or Register  to view this content.
    - If you are using excel 2007: on any cell (within the pivot table) in the desired column, [rightclick Mouse Key], then [T], then [L], then "desired text here" and then [enter].
    where the "[rightclick Mouse Key]" is placed just to the right of the space bar on my keyboard.

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  20. #20
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    THANK YOU FOR YOUR TIME AND HELP MY FRIENDS.
    I still have a problem when apply the filter. The macro runs and runs and I have to alt+ctrl+del to close excel. Also the filter shows the new selection as well as the old one.
    I m desperate!!!!

  21. #21
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot table data selection

    I will try to get someone better at this to look at it for you....

  22. #22
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    The issue: This excel program is made in order to print the post-office labels by printer.
    So the user has to go to ΄ΠΕΛΑΤΕΣ' (CUSTOMERS) pivot table and then to the filter button. After that a user has to press twice select all in order to select and deselect the ticks. Then to write down in the field the customer ID and then to tick the box in order to apply the filter. This is the process I need to bypass in order to make things a little more easy for unfamiliar users.

  23. #23
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table data selection

    While we wait for someone better, see if this works for you.
    Attached Files Attached Files
    Last edited by romperstomper; 06-09-2011 at 08:44 AM.
    Remember what the dormouse said
    Feed your head

  24. #24
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    Quote Originally Posted by romperstomper View Post
    While we wait for someone better, see if this works for you.
    Thanx a lot. It is almost what I need. However because we have thousands of customer IDs, this drop down only works by draging the sidebar and thus a user will hover around the numbers to find a specific ID. Nevertheles the good thing (for me) is that you guys get what I need to do and you really help me. Thanx again for your time..

  25. #25
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table data selection

    If you would like an autocomplete feature, we can do that using a combobox control instead?

  26. #26
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    Quote Originally Posted by romperstomper View Post
    If you would like an autocomplete feature, we can do that using a combobox control instead?
    Yep thats exactly what I need. And I also need it for the other pivot table named "ΒΑΡΟΣ".
    Please let me know how to do it....

  27. #27
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table data selection

    Try this one.
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    Thanx for the file, however using a combo box is very slow due to the large number of entries.
    Nevetheles I think I found the solution to my issue: I deleted the pivot tables and I create a vlookup function for both customers(ΠΕΛΑΤΕΣ) and weight(ΒΑΡΟΣ). Some times the solution is easier then expected. Thanx a lot for your time..

  29. #29
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    How can I make the cursor, when I press enter, to bypass specific cells and go to some predefined cells?

  30. #30
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table data selection

    That's a completely different question so can you post it as a new thread please?

  31. #31
    Registered User
    Join Date
    02-15-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Pivot table data selection

    yes your are right, sorry.

+ 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