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