Drag handle - linking columns to rows

[mpfsfood]

Greetings,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I need to link a column of numbers in one worksheet to a row of another worksheet. We tried the transpose feature but that transposed static values instead of a live link. Here is what I’m working with.
<o> </o>
Worksheet 1<o></o>
A B C D E F .<o></o>
1 25 . <o></o>
2 38 .<o></o>
3 1 .<o></o>
4 10 .<o></o>
5 35 .<o></o>
6 58 <o></o>
<o> </o>
<o> </o>
<o> </o>
Worksheet 2<o></o>
A B C D E F .<o></o>
1 . <o></o>
2 .<o></o>
3 Amount =Sheet1!C1 =C2 =C3 =C4 =C5 .<o></o>
4 .<o></o>
5 .<o></o>
6 <o></o>
<o> </o>
Worksheet 1 has about 50 rows of numbers and I’ve got a total of 70 similar worksheets to link to Worksheet 2. In B3 above, I’ve tried typing =$C1 and =C1.
<o> </o>
After linking B3 to C1 in worksheet 1, I try to use the drag handle to continue the series. But what ends up happening is Excel copies =C1, =D1, =E1. What I need Excel to do is fill in =C1, =C2, =C3 when I use the drag handle across the row.
<o> </o>
Any thoughts?
<o> </o>
Thank you.

 

[Derek Brown]

I haven't studied your example but picked up on what you said in the second sentence.
The TRANSPOSE function should 'link' from the target sheet to the source sheet.
For example, if you want to link Cells A1:B6 on Sheet1 to Cells A1:F2 on Sheet2 you would do the following:
On Sheet2, select cells A1:F2 and enter the formula:
=TRANSPOSE(Sheet1!A1:B6)
BUT this must be entered as an ARRAY formula using Ctrl+Shift+Enter (and not just using Enter).
Changes in cells A1:B6 on Sheet1 will then be reflected in Sheet2.
WARNING: You will not be able to delete individual cells within that array formula (you would have to delete the complete row and re-enter the formula).