Thanks for your response. But I don't think you under my request. I do need similar way of transpose with formulas. So eveytime I change the value in sheet1 it should change in sheet2. I can do one by one copy and pastlink to do this task. But I don't want to do one at a time. I want something copy one and drag. Is it possible?
I don't understand, there appear to be 2 issues here, first you mention copy and drag and then talk about doing it one cell at a time with transpose, just for the record to use transpose
highlight all the required cells on sheet1 ie c3 to c10 select Copy
select cell D5 on sheet2 select Edit, pastspecial, check Transpose and click OK
D5 to K5 will now contain the data.
What have Formulas got to do with this?
If you want a Cell on Sheet2 to equal a cell on Sheet1 then =sheet1!c3 for example, does the trick.
I just want to do what you mentioned on your last line. But I want to do the same along line three (left to right) on sheet2. I don't want to copy one cell at a time. Anything like draging along line three?
I am still unclear, it now seems as though you want to type
=Sheet1!c3 into a cell and then drag that along a row with the row number changing, that is not possible as far as I know, dragging along a row will cause the column reference to change, this can be avoided by using absolute addressing
Then this will appear in each cell thus leaving you to change the row number.
This appears to be a different question than the original of copying from sheet1