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