Pivot chart generated by selection

This isn't a pivot chart it makes use of named dynamic ranges. It looks like you want the top 20 from each product. Fortunately your data is already sorted this way. You can't use a regular pivot table since the data is not contiguous. In fact, it's on multiple pages.

I kept your helper sheet, but I don't use it. My work is done on the Parameters Sheet. Here I have a list of products and what sheets they are on. Cell E1 is the sheet name based on the selected product. Cell E2 is the "base cell" used by the offset command.

I use the base cell to get the product list: =OFFSET(INDIRECT(Base_Cell),0,0,20,1) - this reference means start in the based cell (cell A8 on the sheet for the selected product), go down zero rows, go right zero columns and give me a range 20 rows deep and 1 column wide. This gives me the names of the top 20 products. I called this name Product_List.

The values are given by: =OFFSET(Product_List,0,6) which means look at the Product_List and go 6 columns to the left. I called this name Association.

Here are two wikis that explain how to create named dynamic ranges and how to use them for dynamic charting.
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

The big wrinkle in this one is that I had to use INDIRECT for the base cell.

If you go to the the Formulas Ribbon and select Name Manager, you will see the named ranges and their definitions. If you select a name and go down to the Refers To box and click in it, you will see what that range currently points to.

Also, if you go to a blank sheet (or a blank part of a sheet) and press the F3 key, you will get a list of names. There is a button to press on the dialog box that shows the names and this will copy / paste the names and their definitions into whatever cell is active.

The wikis will give you more information on named ranges. They are kind of handy because they can be dynamic so formulas, lookups or whatever that reference them won't have to change when data gets added to or taken away from the range. Also sometimes you can assign a static range of cells to a named range. This makes it easier to understand formulas. For example: VLOOKUP(A2,Region_List,2,False) is a lot easier to debug than something like VLOOKUP(A2,'Lookup Sheet'!$A2:$C$28,False).