Using name formulas as a series in pie graph

**gordian** · 03-09-2010, 03:34 PM

Howdy everyone. Apologies if this question is rudimentary--I've done a few searches and haven't found anything resembling the solution I'm after. Anyway...

I'm using Excel 2003. I have several names which are actually formulas. Ex:

Sheet1.xls!PercentA = SUM((C15+F15+I15)/(C18+F18+I18))
Sheet1.xls!PercentB = SUM((C16+F16+I16)/(C18+F18+I18))
Sheet1.xls!PercentC = SUM((C17+F17+I17)/(C18+F18+I18))

When I throw these into cells using "=PercentA" they're fine by themselves but I would like not to have them in cells at all.

I'd like the names to be part of a pie chart, but if I enter the series as:

=Sheet1.xls!PercentA,Sheet1.xls!PercentB,Sheet1.xls!PercentC

I get: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference."

Any help getting this squared away would be greatly appreciated.

Thank you!

**teylyn** · 03-09-2010, 07:42 PM

Hi gordian,

you can't reference range names in data series that way. Put your range names into cells and chart off these cells. If need be, put the cells with the range names on a hidden sheet.

**gordian** · 03-09-2010, 07:48 PM

teylyn,

Thank you for the response.

I think perhaps my issue was not made clear, so I've attached a small example to illustrate my goal. Thank you again!

**teylyn** · 03-09-2010, 08:09 PM

see attached pie chart. You can not reference the data points for a chart from individual range names. You need to have the values in cells and create your pie chart off those cells. Of course, you can show the pie chart labels as percentages.

As I said above, if you don't want the cells for the pie chart to show in this sheet, put them in another sheet.