I have basic values in sheet named SOURCE
i have aggregate values in sheet named TARGET
The values shown in B4:D4 in sheet SOURCE is an outcome of a formula.I want this values to be copied to B2:D2 in sheet named TARGET
likewise the values shown in B8:D8 in sheet SOURCE to be copied to B3:B3 in sheet TARGET. Likewise the data in B20:D20 to be copied
i can do this manually by copy and paste special-values.
But is there any way to done it automatically by excel? thanks
the sample file is attached.

Hi,

Am Sun, 16 Mar 2014 17:12:16 +0000 schrieb sumesh56:

I have basic values in sheet named SOURCE
i have aggregate values in sheet named TARGET
The values shown in B4:D4 in sheet SOURCE is an outcome of a formula.I
want this values to be copied to B2:D2 in sheet named TARGET
likewise the values shown in B8:D8 in sheet SOURCE to be copied to B3:B3
in sheet TARGET. Likewise the data in B20:D20 to be copied
i can do this manually by copy and paste special-values.

in B2 of TARGET:
=VLOOKUP($A2,source!$A$1:$D$20,COLUMN(),0)
and copy to right and down.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.