Hi All,

I have the Following in my worksheet

A1 contains a month end date for eg. 30-04-05

B2 C2 ..... AK2 AM2
31-1-03 28-2-03 ..... 31-12-05 TOTAL
100 250 ..... 425 xxxx
250 150 ..... 200 xxxx

I want to get the total based on the date in my A1. For eg if A1
containd 31-3-04 in my AM3 the total shud be sum(B3:P3) (31-01-03 to
31-03-04)

so when ever I change the date in my A1 my total column shud take from
31-1-03 up to the column in which the same date appears.

Is there an easy way to acheive this either thru code or formulae?

thanks

=SUM(OFFSET($B3,,,,MATCH($A$1,2:2,0)-1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Soniya" wrote in message
oups.com...
Hi All,

I have the Following in my worksheet

A1 contains a month end date for eg. 30-04-05

B2 C2 ..... AK2 AM2
31-1-03 28-2-03 ..... 31-12-05 TOTAL
100 250 ..... 425 xxxx
250 150 ..... 200 xxxx

I want to get the total based on the date in my A1. For eg if A1
containd 31-3-04 in my AM3 the total shud be sum(B3:P3) (31-01-03 to
31-03-04)

so when ever I change the date in my A1 my total column shud take from
31-1-03 up to the column in which the same date appears.

Is there an easy way to acheive this either thru code or formulae?

thanks

Hi Soniya!

first:
use a cell with a formula to find the the date in row2
B1 =MATCH(a1,2:2,0)

then:
you need indirect or offset function.
the offset function has 5 arguments

range,moveR,moveC,height,width

let's set the 'starting range' at b3.
we're not "moving" it, just "sizing" so argument 2/3 will be zero.
b1 holds position so we must create the width by deducting 1

AM3 =SUM(OFFSET($B3,0,0,1,$B$1-1)

try it..

....

got it?

now it's also easy to adapt the formulas
so you can sum from/to.

you'll need a cell with the From position.
you'll need a cell with the Thru position.

assume a1 is date from
assume b1 is date thru
c1 (position from) =MATCH(a1,2:2,0)
d1 (position thru) =MATCH(b1,2:2,0)

am3 = =SUM(OFFSET($A3,0,$C$1,1,$D$1-$C$1+1)

(i hope i got all the plus/minus 1 correct..
as i'm typing "off the cuff")




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Soniya wrote :

Hi All,

I have the Following in my worksheet

A1 contains a month end date for eg. 30-04-05

B2 C2 ..... AK2 AM2
31-1-03 28-2-03 ..... 31-12-05 TOTAL
100 250 ..... 425 xxxx
250 150 ..... 200 xxxx

I want to get the total based on the date in my A1. For eg if A1
containd 31-3-04 in my AM3 the total shud be sum(B3:P3) (31-01-03 to
31-03-04)

so when ever I change the date in my A1 my total column shud take from
31-1-03 up to the column in which the same date appears.

Is there an easy way to acheive this either thru code or formulae?

thanks

Fantastic.. and worked well.... thanks for all the reply..

thanks again