Slicers vs Filters

I have attached a sample workbook that I am trying to figure out a few things. I would like to create a chart that has a filter so I can show a chart based on a specific item. The workbook I have attached has 2 worksheets. The first worksheet, "Summary," contains all of the data for the charts I created on the second worksheet. I created one chart with a slicer and the other chart using the index function and a drop down button. I like that with the slicer, I can just see the data related to the item and not all of the failures like with the chart below it. However, I do not like the look or how big the filter box is for the slicer and like the look of the drop down button. Is there a way to format either the slicer filter to look like the drop down button or for the drop down button chart to NOT include data that is not relevant to the item listed in the drop down button?

Also, if any body knows how to accomplish this with a macro, please point me in the right direction.

Thanks!!!!

Report%20Charts(1).xlsm


Hi,

I added a macro to break out the data from your list containing all of the #REF! errors into a clean list. Then I created some named ranges to determine the size of the chart data for the selection from the drop down. Take a look and let me know if this works or if you have any questions.

Steve

That is freaking AWESOME!!! Thanks!!

Now, if I want to copy this macro into a different workbook, what is the best way to move it and have it work correctly?

I do not understand (a) How your macro works...I keep getting some range and global errors and (b) not sure what additional formulas or work I need to do in order to make this work?

Ok...I studied your worksheet a little more and realized you had declared some ranges that I had not declared and after I named the same ranges you had on your sheet, I am now getting and "AdvancedFilter method of Range class failed." What do i need to do to fix this?

Hi,

Sorry for the delay in getting back to you. I've been offline. The macro is essentially an advanced filter where the data table is the List Range, the named range "Criteria" is the Criteria Range and the named range "Extract" is the Copy To location. Each time you select a different option from the combo box it calls the macro to pull back the appropriate data. The named range "ChartRng" detrmines the size of the chart data (Rows). The named range "ChartValues" returns the chart values by using the OFFSET of one column from the "ChartRng".

So, if you are copying it into another workbook module you have to make sure that a) you have all the appropriate named ranges, b) you're referencing the correct sheet name for the datatable in the macro ("With Sheets("insert your sheet name")), c) using the named range "ChartValues" as the data source for your charts. If you want to send me the workbook with some sample data I could modify and send to you with all the details.

HTH

Steve

Ok...I thought I had name all the ranges correctly. However, I still was having issues. I have copied the worksheet I am working with to the attached workbook. The name of this worksheet will be the same as in the actual workbook. Thanks for looking at this and let me know where I went wrong so I can avoid this issue in the future.

Thanks!!

Dan

Hi,

Here is your workbook modified with comments. Let me know if you have any questions.

Steve

ChartMacroExplanations.xlsm

Steve,

This is AWESOME and EXTREMELY helpful!!! I have another question...if I were to change the pivot table to show just the top 3 for each item, would that have any negative impact on this macro and set-up?

Thanks,
Dan

Hi Dan,

I was able to do this but it was quirky. I opened the workbook and changed the pivot table first then tried to select from the combobox and got the AutoFilter method error. I closed the workbook without saving the changes, reopened and changed my selection in the combobox a couple of times first, then altered the pivot table to show the top 3 only and it worked fine. Not sure why that was but it worked.

Steve

Ok...I got this to work...now I have a goofy issue...is there a way to not have the worksheet jump when it refreshes? I know it is a weird request but it is a little annoying when the whole screen jumps because of the macro running...is there a way around this?

Hi,

Add "Application.ScreenUpdating = False" as the second line of the macro. I forgot to add that in. You'll notice I have "Application.ScreenUpdating = True" at the end of the macro.

Steve

BEAUTIFUL!!!! Thank you so very much!!!!!