L
linda
Hi!
in Sheet2:
B1=IF(COLUMNS($A:A)>COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!$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!
in Sheet2:
B1=IF(COLUMNS($A:A)>COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!$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!