thanks for the reply. but the result is erraneous. in all the columns from B2 in target i get 99 which is the value of B4 of the sheet source.

on further examination i found that when i double click on c2 where 99 is displayed, the formula is shown there. then when i click on anywhere else, i get the correct value on c2. i get the correct value in all other cells from d2:d4 by doing this excersise(first double click on the cell then somewhere outside the cell and i get the correct value(total value).99,101,103 etc

your formula is great . it works. i was wrong in putting the formula in cells other than the one specified in the formula($A2). now i found out the mistake.thanks a lot for the help. have a nice day.