Excel copy formula

Question

I have two sheets, In first sheet I have data in row and I need to write a formula in sheet 2 to capture one by one value in column without manual writing of all fomulas.

Answer 1

Refer below Images:

Image of Sheet1:

Image of Sheet2:

Enter below formula in Sheet2 in cell A1 & copy down & to the right:

=IF(OR(COLUMN()-COLUMN($A$1)>=ROWS(Sheet1!$A$1:$C$11),ROW()-ROW($A$1)>=COLUMNS(Sheet1!$A$1:$C$11)),"",OFFSET(Sheet1!$A$1,COLUMN()-COLUMN($A$1),ROW()-ROW($A$1)))

In the formula,

$A$1 refers to - cell in Sheet2 from where you wish to start;

Sheet1!$A$1:$C$11 refers to - range in Sheet1 which you wish to copy.

The above formula is dynamic so that you may start the "Transpose" from any cell of Sheet2 - just replace $A$1 in the formula to the cell address where you may want to start the "Transpose" - so that if you wish to start from cell E2 in Sheet2, the formula will be updated to:

=IF(OR(COLUMN()-COLUMN($E$2)>=ROWS(Sheet1!$A$1:$C$11),ROW()-ROW($E$2)>=COLUMNS(Sheet1!$A$1:$C$11)),"",OFFSET(Sheet1!$A$1,COLUMN()-COLUMN($E$2),ROW()-ROW($E$2)))

Similarly you may have any range in Sheet1, just update the range Sheet1!$A$1:$C$11. So that if you wish to change the range in Sheet1 to C4:E14 & in Sheet2 the start cell is E2, the formula will become:

=IF(OR(COLUMN()-COLUMN($E$2)>=ROWS(Sheet1!$C$4:$E$14),ROW()-ROW($E$2)>=COLUMNS(Sheet1!$C$4:$E$14)),"",OFFSET(Sheet1!$C$4,COLUMN()-COLUMN($E$2),ROW()-ROW($E$2)))

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Transpose_UsingFormulas.xlsx

Regards,

Amit Tandon

Answer 2

in A1 of Sheet2 write the below formula and drag down and across. 

=IF(Sheet1!A1<>"",Sheet1!A1,"")

Note: Drag Down Shortcut Key is : CTRL + D

         Drag Across Shortcut Key is CTRL + R

Answer 3

Thank you Karthick for responding but I need to the data would in as below format, means I need to capture values from row (from sheet 2) into column (sheet 1). Either formula or VBA code?

Answer 4

Give this formula in Sheet2 at cell A1 and copy it right and down.

=IF(OFFSET(Sheet1!$A$1,COLUMN(A1)-1,ROW(A1)-1)="","",OFFSET(Sheet1!$A$1,COLUMN(A1)-1,ROW(A1)-1))

Answer 5

Apologies for misunderstanding your requirement. Please see V_Agarwal Answer