Dynamic Pie Chart?

[Fujirich]

I'm trying to figure out how to make a pie chart dynamically adjust both in terms of the data labels and the percentages, and not having much success so far. I found this video (https://www.youtube.com/watch?v=T8P8xMBkH4I) which seems to get close to the idea, but I'm finding that my need does't exactly fit. Like the video, I want to make this work via formula, and avoid VBA and array formulas if at all possible.

This is a really simple set up, but since the workbook will likely be used by others in my organization, I need it to work dynamically for the conditions they will input. Here's the description...

-- In A1:A9, I have A1 as a heading (Media Type), and then A2:A9 are data validated cells that allow the user to pick names from a list (all text)

-- In B1:B9, B1 is the heading (% of Work), with B2:B9 as % formatted cells in which the users will enter numbers to add up to 100% in total.

So in A2:A9, users will select the media type(s), and assign the % of work each one represents in the cell to the right. I don't expect it they will use all 8 rows too often, but I've provided that as an upper limit.

I want to have a pie chart generated based on any data selected and input, dynamically adjusting whether the user enters one row of data or all 8.

Any thoughts on the best way to achieve this?

 

[SpillerBD]

Get your Data in a Table, CTRL+T.
With a cell in the Table selected, press F11.
Use the Ribbon item, Move Chart, to move back to the desired sheet.
Select, a cell in the Table. Under the Table Tools/Design, click Inset Slicer. Choose the "Media Type" label.

 

[Fujirich]

I saw the idea of placing data in a table to solve this problem, but I haven't figured out how to make it work. I get the same chart results either way.

I'm able to link the data from cells the user will interact with elsewhere on the sheet, so I can create the chart on the same page I want it on, eliminating the need to move it. So I did the following...

-- I copied headings into U1 & V1

-- I linked A2 to U2 via a simple =A2 formula, did the same for the next column, and copied down 8 rows

-- I highlighted U1:T9, went to Insert - Table to convert the range to a table

-- Then I tried creating a pie chart from the range U1:T9. That results in a heading of "Media Type" and a legend of 1, 2, 3, etc. Since I only had data in the first three rows, no chart was plotted.

So unless I'm missing something (highly likely) the conversion of the data to a table doesn't solve the problem of the pie chart adjusting dynamically to whatever rows have selected/entered data or not.

Thanks for trying to help - but perhaps something else is needed.

 

[SpillerBD]

Mike Girvin's Series.