I think you want to go with Dynamic Named Ranges for both the chart's
Category Labels and Values. These use worksheet functions (particularly the
Offset function) in the ranges' RefersTo property to allow automatic
adustment of the range definitions. Debra Dalgleish discusses this:
http://www.contextures.com/xlNames01.html
Example:
1. Open the Define Name dialog: Select Insert > Names > Define
2. Now create the Category Labels range:
i. In the "Names in workbook:" window enter the name "CatLabels"
ii. In the "Refers to:" window enter the following formula:
=OFFSET(Sheet1!$B$1, 0, 0, MAX(COUNTA(Sheet1!$B:$B), 1), 1)
(See <Note> below)
iii. Click the Add button
3. Now create another range for the Values:
i. In the "Names in workbook:" window enter the name "Vals"
ii. In the "Refers to:" window enter:
=Offset(CatLabels, 0, 1)
iii. Click the Add button
4. Click the Close button
5. Now change the chart's source data to reference these names:
i. Right-click the chart and select Source Data
ii. Select the Series tab (if not already active)
iii. Ensure that "Series1" is selected in the "Series" window
iv. In the "Values:" window enter: =Sheet1!Vals
v. In the "Category Labels:" window enter: =Sheet1!CatLabels
The above assumes that the worksheet is named "Sheet1". Also assumed is that
the Category Labels (stock names) are all in column B and there are no other
data below this. The same is assumed for the column C data (number of shares
?). As you add/delete data to these ranges the chart should update
automatically. I never use Pie charts but it is evident that they
automatically calculate the percentages, so the fourth column (D) is not
required by the chart.
As far as the fourth column is concerned, I would:
1. Insert this formula in cell D1:
=IF(C1 = "", "", 100*C1/SUM(Vals)).
2. AutoFill the forumla down to the maximum range that you would ever need.
Alternative, use code in the WorksheetChange event to automatically adjust
this range to match the adjacent Dynamic Named ranges or just AutoFill them
as required when you add/delete the stock data.
< Note > The conventional formula would have been:
=OFFSET(Sheet1!$B$1, 0, 0, COUNTA(Sheet1!$B:$B), 1)
The formula I used is a slight departure that includes the Max function to
ensure that the range definition is never empty (at least one cell) and thus
avoids an error if there are no data.
The Copy and Paste functions are not available when either the Define Names
and Source Data dialogs are active. To copy use:
<Ctrl> + C
To paste use:
<Ctrl> + V
Regards,
Greg
