|
I've got two questions I could really use a hand on. First, i've got a pivot table setup with a filter for Invoice Numbers and the need to select multiple items. When just one invoice is select, the drop down box shows that invoice number. If more than one is selected it just shows (Multiple Items). Is there a way to get a copy of the multiple items select to print on the report without manually typing them in somewhere? Secondly, I also have a Data Validation list with hundreds of choices referencing data in another sheet of a workbook. Is it possible to get it to filter down when someone clicks that cell and starts typing instead of having to scroll through the list by hand?
|
#
¿
Nov 2, 2010 20:25
|
|
|
|
| # ¿ May 6, 2024 05:05 |
|
|
TheAngryDrunk posted:Not sure I understand the first question, but the answer to the second one is 'no.' You can stick a combo box on the worksheet and it will fill in the first item on the data validation list that starts with the same letter, but you can't do it with a cell. Didn't think so  Oh well.In response to the second, here are some screen shots to hopefully help explain. When you select one item to filter by, it shows the item in the list like in this picture:  When you select multiple items to filter by, it shows (Multiple Items) We then have to manually type in the following row those items. I'd like to avoid them having to type those in and have it print out the selected filter items somewhere. 
|
|
#
¿
Nov 2, 2010 21:12
|
|
|
Sub Par posted:Quick and dirty solution in VBA. Create a module, create this sub: Sub Par, thanks for that, it does what I need but has a small quirk. It's pulling in random Timesheets that were used previously for testing, but that is no longer listed anywhere in the work book. See the picture for the filter items available and what it's printing.  Is there a way to clear those out somehow?
|
|
#
¿
Nov 3, 2010 17:24
|
|
|
Sub Par posted:Did you refresh the pivot after clearing the data out of the workbook? I did fresh it but that didn't help. After some screwing around with it this morning I tracked down the problem. Under the Pivot Table Options, and the Data tab there is a drop down for "Number of Items to retain per field: Automatic" I changed that to None and it updates correctly now. Thanks for all of the help, this sheet will now work wonders for us.
|
|
#
¿
Nov 4, 2010 14:27
|
|