Having issues with a Macro to get Chart Data

[dwhite30518]

I was given the following code by another Forum user.

Please Login or Register  to view this content.

I keep getting the following error...
"The extract range has missing or illegal field name"

Can someone help me figure out what is wrong?

[Mordred]

Extract is a defined range name?

[dwhite30518]

And I have named this range and yet I still get this error. How do I resolve?

[Mordred]

Try declaring the range for Extract within the code procedure. For instance

Please Login or Register  to view this content.

I don't know for sure if this will solve your problem because I haven't tested it but give it a try.

[dwhite30518]

I tried using your code and put in the range information. This is what the code looks like...

Please Login or Register  to view this content.

And I still get that error. I have attached the workbook with the code that was given to me by the other forum user and you can look this over and help me figure out why it will not work on the real workbook. I also attached the original sample workbook I was using.

Thanks!

[vandan_tanna]

is this what you rea looking for:

Please Login or Register  to view this content.

[dwhite30518]

As you can see on from the attached workbook. I have created a pivot table from all of the repair data from multiple worksheets with in my report workbook. I am trying to create a chart that shows me just the top 3 failures and has a drop down button that I can use to select individual items and see the failures. I know I can create this using the pivot chart and slicer but I do not like the look of the slicer and would prefer to do this with a drop down button. I would also like to be able to create a macro so I can run this month after month automatically, and not spend so much time on creating it.

[dwhite30518]

It would help if I included the workbook.

[vandan_tanna]

Hi:

I am sure this workbook makes perfect sense to you but it is hard for me to follow.

I will ask others to help.

[dwhite30518]

I have got it working...I am good....Thanks!

[tigeravatar]

dwhite30518,

Attached is a modified version of your Report Charts workbook. This workbook is VBA free and uses a Data Validation list to update the chart.

I removed the pivot tables for this particular scenario because I don't think they were working out quite the way you wanted them to. Instead, I have some index/match/countif formulas to pull the unique items list and their failure counts.

I pulled the list of unique items from the Items List. This is performed by using this formula in the 'Summary' worksheet, cell G2 and copied down to G61:

Please Login or Register  to view this content.

Note: To get them to be listed alphabetically, I sorted on column A then on column B in the Summary worksheet.
Becuase of the dynamic named ranges and that this formula is copied down to row 61, it will automatically pick up new unique entries as they are added to the Items list in sheet 'Summary', column A. (To keep the unique list in alphabetical order, you would add items to the end of the Items list, and then do a Sort, first level on Items (column A), next level on Actual Failures (column B)).


Next I pulled the unique list of Actual Failures. This is performed by using this formula in the 'Summary' worksheet, cell H2 and copied down to H61:

Please Login or Register  to view this content.

Same notes apply to this as to the unique item list.


Now that we have these unique lists, I created a named range called ItemList with this formula:

Please Login or Register  to view this content.

I used that as the data validation list for the 'Charts' sheet cell B2.


Next was to get the failure counts. These are dependent on what is chosen from 'Charts' sheet cell B2. In the 'Summary' sheet cell I2 and copied down to I61 is this formula to get the counts:

Please Login or Register  to view this content.

Now that we have the counts, we can rank them to get the top 3 values. The rank formula is in 'Summary' sheet cell J2 and copied down to J61:

Please Login or Register  to view this content.

Now we have identified what the top failures are for any given item. We can extract this information to the Charts page so that we can clearly see what the top failures are. In 'Charts' sheet cell A6 and copied over and down to cell B8 is this formula:

Please Login or Register  to view this content.

That range is what the chart is created from. Now we can select an item from the data validation drop-down list, and the chart will automatically update to show the top 3 failures of the selected item.

[tigeravatar]

I have got it working...I am good....Thanks!

lol, should have refreshed page before posting that monster

Glad to hear you got it working

[vandan_tanna]

Impressive work tigeravatar!

New workbook is a lot easier to follow (nicely laid out) and is robust!