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.

Sorry i have left out something to clarify. In my excel file, row 22 is my
Date and row 23 is my Total Sales. The rows for Prod A, Prod B, and Prod C
have been placed another range in another sheet. Thats where row 22 and 23
appears in my 2 named range SALES and DATE.

"Tan" wrote:

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.

If you don't want to display 0 for a total that doesn't yet exist, try
something like this

=if(sum(A1:A10)=0,NA(),sum(a1:A10))

Values that are NA() are not displayed in charts.

"Tan" wrote:

Sorry i have left out something to clarify. In my excel file, row 22 is my
Date and row 23 is my Total Sales. The rows for Prod A, Prod B, and Prod C
have been placed another range in another sheet. Thats where row 22 and 23
appears in my 2 named range SALES and DATE.

"Tan" wrote:

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.