Hi All,

I have the following data on worksheet 5 for example and is arranged like
this:

Date        ...........Nov'06    Dec'06     Jan'07     Feb'07     Mar'07    
Apr'07    May'07
Prod A     ...........1000       1000       1000       1000       1000      
 blank     blank
Prod B     ...........1000       1000       1000       1000       1000      
 blank     blank
Prod C     ...........1000       1000       1000       1000       1000      
 blank     blank
Total Sales          3000       3000      3000        3000       3000        
0            0

The cell under Apr'07, May'07, Jun'07 ...etc in the Total Sales row carries
a summation formula to total up the units sold for the 3 products. It shows
zero becos its a new month for Apr'07, May'07, Jun'07...etc and no units sold
are recorded as yet. Indeed, summation formula are created in the Total Sales
row from Apr'07 onwards till say year 2010 as end users dont want to put in
the summation formula themselves after they had input sales units for the new
month for Product A, B, and C.

I have also created 2 named range, called DATE and SALES.

DATE refers to
'=OFFSET(Sheet5!$A$22,0,(COUNTA(Sheet5!$23:$23)-12),1,(COUNTA(Sheet5!$23:$23)-(COUNTA(Sheet5!$23:$23)-13))-1)'

SALES refers to
'=OFFSET(Sheet5!$A$23,0,(COUNTA(Sheet5!$23:$23)-12),1,(COUNTA(Sheet5!$23:$23)-(COUNTA(Sheet5!$23:$23)-13))-1)'

The rational behind these 2 formula is to detect the last cell (latest input
by end user) which carries a figure under the Total Sales row and then offset
up to pick the month in order to refer in the chart.

The problem here is how to ignore those subtotal formula cell that shows
zero from Apr'07 onwards.

Thanks.