can use a user-defined function in chart value source？
 

Dear MVPs

When I draw a line chart by wizard, it asks me to input the value source for
a serie.
Actually I need do some filtering about that source, can I use a
self-defined function
here, but not only those excel functions?

can use a user-defined function in chart value source？
 

If can, would you please show me a example on how to write it. Thanks

"Gw" wrote:

Dear MVPs

When I draw a line chart by wizard, it asks me to input the value source for
a serie.
Actually I need do some filtering about that source, can I use a
self-defined function
here, but not only those excel functions?

can use a user-defined function in chart value source?
 

The only formula allowed here is a link to a worksheet range or a defined
name. You can make the worksheet range as fancy with formulas as you want,
and you can define fairly intricate names as well. I prefer to do the fancy
calculations in the worksheet, and save the names for definitions of dynamic
worksheet ranges.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Gw" wrote in message
...
If can, would you please show me a example on how to write it. Thanks

"Gw" wrote:

Dear MVPs

When I draw a line chart by wizard, it asks me to input the value source
for
a serie.
Actually I need do some filtering about that source, can I use a
self-defined function
here, but not only those excel functions?

can use a user-defined function in chart value source?
 

For a simple example, I write a function as below:
Function FeedData(star, number) As Range
FeedData = Application.WorksheetFunction.Offset(Data1!r4c1, 0, 0, 72, 1)
End Function

and write "=FeedData(0,1)" as chart value source, but it doesn't work yet.
Do you think where need to correct? Thanks

Gw

"Jon Peltier" wrote:

The only formula allowed here is a link to a worksheet range or a defined
name. You can make the worksheet range as fancy with formulas as you want,
and you can define fairly intricate names as well. I prefer to do the fancy
calculations in the worksheet, and save the names for definitions of dynamic
worksheet ranges.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Gw" wrote in message
...
If can, would you please show me a example on how to write it. Thanks

"Gw" wrote:

Dear MVPs

When I draw a line chart by wizard, it asks me to input the value source
for
a serie.
Actually I need do some filtering about that source, can I use a
self-defined function
here, but not only those excel functions?

can use a user-defined function in chart value source?
 

As you've discovered, that's not how it works. Your FeedData is a user
defined function (UDF), not a formula that links to a range or name in the
worksheet.

To name a range, go to Insert menu Names Define.

Type a name in the Name box:
FeedData

Enter a formula in the Refers To box:
=OFFSET(Data1!R4C1,0,0,72,1)

Click Add, and repeat for additional names you need to define.

Return to the chart's Source Data dialog, Series tab, and where appropriate
enter:
=Data1!FeedData

If 'star' and 'number' are used in the formula (I don't see how, because
they were absent in your ersatz UDF), use them in the offset formula, e.g.:
=OFFSET(Data1!R4C1,Data1!R1C1,Data1!R2C1,Data1!R3C 1,1)

For more on this topic, see the examples and links he
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Gw" wrote in message
...
For a simple example, I write a function as below:
Function FeedData(star, number) As Range
FeedData = Application.WorksheetFunction.Offset(Data1!r4c1, 0, 0, 72, 1)
End Function

and write "=FeedData(0,1)" as chart value source, but it doesn't work yet.
Do you think where need to correct? Thanks

Gw

"Jon Peltier" wrote:

The only formula allowed here is a link to a worksheet range or a defined
name. You can make the worksheet range as fancy with formulas as you
want,
and you can define fairly intricate names as well. I prefer to do the
fancy
calculations in the worksheet, and save the names for definitions of
dynamic
worksheet ranges.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Gw" wrote in message
...
If can, would you please show me a example on how to write it. Thanks

"Gw" wrote:

Dear MVPs

When I draw a line chart by wizard, it asks me to input the value
source
for
a serie.
Actually I need do some filtering about that source, can I use a
self-defined function
here, but not only those excel functions?