Hi!

in Sheet2:

B1=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(S heet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
-copy accross 40 columns

A2=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
-copy accross 40 rows

B2=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1 :$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
-copy accross 40rows*40columns

based on the function above,it will create a table of data where i will
create a chart from it.i define 2 offset to create the chart:

CUST=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1 )
MOD=OFFSET(Sheet2!$B$2,0,0,1,COUNTA(Sheet2!$2:$2))

for now the chart has 25R*24C,therefore when i ceate the chart,it is based
on 40R*40C,included the row and column with no data.is it because of the
function in the cell?
i copy the function to 40R*40C as a space for new data since the table would
expand rows and column time by time.
the problem right now is i want the chart include the available data only..i
dont want it to include the zero data.is it possible?is yes,how?

thanks in advanced!

--
Regards,
Linda

Linda -

Here is what I posted in response to a similar question last month:

Using dynamic ranges, you can create a chart whose series grow and shrink as
the range becomes longer and shorter. But you can't use dynamic ranges to
vary the number of series in the chart. That would require some VBA.

Assuming the data starts in cell A1, you could define a name that includes
the rows and columns up to the errors. Go to Insert menu Name Define:

Name: ChartDataRange
Refers To:
=offset($A$1,0,0,COUNTA($A$1:$A$36)-SUM(IF(ISNA($A$1:$A$36),1,0)),COUNT($1:$1)+1)

I arbitrarily chose A1:A36; pick a range long enough to get all the values
you may ever need.

Update the data, then run this macro:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("ChartDataRange"), _
PlotBy:=xlRows
End Sub

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

"linda" wrote in message
...
i've gone through all the post and others link but all of the example just
for static column..just define offset for each column.but now,my table is
changing both rows & column.i cant define for each column since there may
be
column with zero data.
my table full with function not only in the value,but also in the labels
for
X & Y axis.

please someone do help me..thank you!
--
Regards,
Linda


"linda" wrote:

Hi!

in Sheet2:

B1=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(S heet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
-copy accross 40 columns

A2=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
-copy accross 40 rows

B2=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1 :$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
-copy accross 40rows*40columns

based on the function above,it will create a table of data where i will
create a chart from it.i define 2 offset to create the chart:

CUST=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1 )
MOD=OFFSET(Sheet2!$B$2,0,0,1,COUNTA(Sheet2!$2:$2))

for now the chart has 25R*24C,therefore when i ceate the chart,it is
based
on 40R*40C,included the row and column with no data.is it because of the
function in the cell?
i copy the function to 40R*40C as a space for new data since the table
would
expand rows and column time by time.
the problem right now is i want the chart include the available data
only..i
dont want it to include the zero data.is it possible?is yes,how?

thanks in advanced!

--
Regards,
Linda

i already try that yesterday,but its still not help.my chart is still 40R*40C
eventhough the available data only 26R*25C.

--
Regards,
Linda


"Jon Peltier" wrote:

Linda -

Here is what I posted in response to a similar question last month:

Using dynamic ranges, you can create a chart whose series grow and shrink as
the range becomes longer and shorter. But you can't use dynamic ranges to
vary the number of series in the chart. That would require some VBA.

Assuming the data starts in cell A1, you could define a name that includes
the rows and columns up to the errors. Go to Insert menu Name Define:

Name: ChartDataRange
Refers To:
=offset($A$1,0,0,COUNTA($A$1:$A$36)-SUM(IF(ISNA($A$1:$A$36),1,0)),COUNT($1:$1)+1)

I arbitrarily chose A1:A36; pick a range long enough to get all the values
you may ever need.

Update the data, then run this macro:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("ChartDataRange"), _
PlotBy:=xlRows
End Sub

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

"linda" wrote in message
...
i've gone through all the post and others link but all of the example just
for static column..just define offset for each column.but now,my table is
changing both rows & column.i cant define for each column since there may
be
column with zero data.
my table full with function not only in the value,but also in the labels
for
X & Y axis.

please someone do help me..thank you!
--
Regards,
Linda


"linda" wrote:

Hi!

in Sheet2:

B1=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(S heet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
-copy accross 40 columns

A2=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
-copy accross 40 rows

B2=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1 :$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
-copy accross 40rows*40columns

based on the function above,it will create a table of data where i will
create a chart from it.i define 2 offset to create the chart:

CUST=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1 )
MOD=OFFSET(Sheet2!$B$2,0,0,1,COUNTA(Sheet2!$2:$2))

for now the chart has 25R*24C,therefore when i ceate the chart,it is
based
on 40R*40C,included the row and column with no data.is it because of the
function in the cell?
i copy the function to 40R*40C as a space for new data since the table
would
expand rows and column time by time.
the problem right now is i want the chart include the available data
only..i
dont want it to include the zero data.is it possible?is yes,how?

thanks in advanced!

--
Regards,
Linda

Try to incorporate your more detailed definitions of the width and height of
the plotting range into my simple name definition.

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


"linda" wrote in message
...
i already try that yesterday,but its still not help.my chart is still
40R*40C
eventhough the available data only 26R*25C.

--
Regards,
Linda


"Jon Peltier" wrote:

Linda -

Here is what I posted in response to a similar question last month:

Using dynamic ranges, you can create a chart whose series grow and shrink
as
the range becomes longer and shorter. But you can't use dynamic ranges to
vary the number of series in the chart. That would require some VBA.

Assuming the data starts in cell A1, you could define a name that
includes
the rows and columns up to the errors. Go to Insert menu Name Define:

Name: ChartDataRange
Refers To:
=offset($A$1,0,0,COUNTA($A$1:$A$36)-SUM(IF(ISNA($A$1:$A$36),1,0)),COUNT($1:$1)+1)

I arbitrarily chose A1:A36; pick a range long enough to get all the
values
you may ever need.

Update the data, then run this macro:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("ChartDataRange"), _
PlotBy:=xlRows
End Sub

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

"linda" wrote in message
...
i've gone through all the post and others link but all of the example
just
for static column..just define offset for each column.but now,my table
is
changing both rows & column.i cant define for each column since there
may
be
column with zero data.
my table full with function not only in the value,but also in the
labels
for
X & Y axis.

please someone do help me..thank you!
--
Regards,
Linda


"linda" wrote:

Hi!

in Sheet2:

B1=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(S heet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
-copy accross 40 columns

A2=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
-copy accross 40 rows

B2=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1 :$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
-copy accross 40rows*40columns

based on the function above,it will create a table of data where i
will
create a chart from it.i define 2 offset to create the chart:

CUST=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1 )
MOD=OFFSET(Sheet2!$B$2,0,0,1,COUNTA(Sheet2!$2:$2))

for now the chart has 25R*24C,therefore when i ceate the chart,it is
based
on 40R*40C,included the row and column with no data.is it because of
the
function in the cell?
i copy the function to 40R*40C as a space for new data since the table
would
expand rows and column time by time.
the problem right now is i want the chart include the available data
only..i
dont want it to include the zero data.is it possible?is yes,how?

thanks in advanced!

--
Regards,
Linda

can't understand what you mean...can you please explain me further?
--
Regards,
Linda


"Jon Peltier" wrote:

Try to incorporate your more detailed definitions of the width and height of
the plotting range into my simple name definition.

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


"linda" wrote in message
...
i already try that yesterday,but its still not help.my chart is still
40R*40C
eventhough the available data only 26R*25C.

--
Regards,
Linda


"Jon Peltier" wrote:

Linda -

Here is what I posted in response to a similar question last month:

Using dynamic ranges, you can create a chart whose series grow and shrink
as
the range becomes longer and shorter. But you can't use dynamic ranges to
vary the number of series in the chart. That would require some VBA.

Assuming the data starts in cell A1, you could define a name that
includes
the rows and columns up to the errors. Go to Insert menu Name Define:

Name: ChartDataRange
Refers To:
=offset($A$1,0,0,COUNTA($A$1:$A$36)-SUM(IF(ISNA($A$1:$A$36),1,0)),COUNT($1:$1)+1)

I arbitrarily chose A1:A36; pick a range long enough to get all the
values
you may ever need.

Update the data, then run this macro:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("ChartDataRange"), _
PlotBy:=xlRows
End Sub

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

"linda" wrote in message
...
i've gone through all the post and others link but all of the example
just
for static column..just define offset for each column.but now,my table
is
changing both rows & column.i cant define for each column since there
may
be
column with zero data.
my table full with function not only in the value,but also in the
labels
for
X & Y axis.

please someone do help me..thank you!
--
Regards,
Linda


"linda" wrote:

Hi!

in Sheet2:

B1=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(S heet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
-copy accross 40 columns

A2=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
-copy accross 40 rows

B2=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1 :$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
-copy accross 40rows*40columns

based on the function above,it will create a table of data where i
will
create a chart from it.i define 2 offset to create the chart:

CUST=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1 )
MOD=OFFSET(Sheet2!$B$2,0,0,1,COUNTA(Sheet2!$2:$2))

for now the chart has 25R*24C,therefore when i ceate the chart,it is
based
on 40R*40C,included the row and column with no data.is it because of
the
function in the cell?
i copy the function to 40R*40C as a space for new data since the table
would
expand rows and column time by time.
the problem right now is i want the chart include the available data
only..i
dont want it to include the zero data.is it possible?is yes,how?

thanks in advanced!

--
Regards,
Linda

I gave an example with a simple definition of my dynamic range. You had a
more complicated definition which presumably accounted for the
characteristics of your data range. I am suggesting you use your name
definitions with the VBA approach to changing the chart's source data range.

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


"linda" wrote in message
...
can't understand what you mean...can you please explain me further?
--
Regards,
Linda


"Jon Peltier" wrote:

Try to incorporate your more detailed definitions of the width and height
of
the plotting range into my simple name definition.

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


"linda" wrote in message
...
i already try that yesterday,but its still not help.my chart is still
40R*40C
eventhough the available data only 26R*25C.

--
Regards,
Linda


"Jon Peltier" wrote:

Linda -

Here is what I posted in response to a similar question last month:

Using dynamic ranges, you can create a chart whose series grow and
shrink
as
the range becomes longer and shorter. But you can't use dynamic ranges
to
vary the number of series in the chart. That would require some VBA.

Assuming the data starts in cell A1, you could define a name that
includes
the rows and columns up to the errors. Go to Insert menu Name
Define:

Name: ChartDataRange
Refers To:
=offset($A$1,0,0,COUNTA($A$1:$A$36)-SUM(IF(ISNA($A$1:$A$36),1,0)),COUNT($1:$1)+1)

I arbitrarily chose A1:A36; pick a range long enough to get all the
values
you may ever need.

Update the data, then run this macro:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("ChartDataRange"), _
PlotBy:=xlRows
End Sub

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

"linda" wrote in message
...
i've gone through all the post and others link but all of the
example
just
for static column..just define offset for each column.but now,my
table
is
changing both rows & column.i cant define for each column since
there
may
be
column with zero data.
my table full with function not only in the value,but also in the
labels
for
X & Y axis.

please someone do help me..thank you!
--
Regards,
Linda


"linda" wrote:

Hi!

in Sheet2:

B1=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(S heet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
-copy accross 40 columns

A2=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!C:C,ROWS($1:1))))
-copy accross 40 rows

B2=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1 :$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
-copy accross 40rows*40columns

based on the function above,it will create a table of data where i
will
create a chart from it.i define 2 offset to create the chart:

CUST=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1 )
MOD=OFFSET(Sheet2!$B$2,0,0,1,COUNTA(Sheet2!$2:$2))

for now the chart has 25R*24C,therefore when i ceate the chart,it
is
based
on 40R*40C,included the row and column with no data.is it because
of
the
function in the cell?
i copy the function to 40R*40C as a space for new data since the
table
would
expand rows and column time by time.
the problem right now is i want the chart include the available
data
only..i
dont want it to include the zero data.is it possible?is yes,how?

thanks in advanced!

--
Regards,
Linda