Let's see if I understand....

On Sheet1 you have valus in Cells A4:E4

On Sheet2, you want formulas that refer to Sheet1 in this way....
B2 refers to Sheet1!A4
B3 refers to Sheet1!B4
C2 refers to Sheet1!C4
D2 refers to Sheet1!D4
E2 refers to Sheet1!E4

and you want to be able to copy that formula scenario down and have the
following:
B5 refers to Sheet1!A7
B6 refers to Sheet1!B7
C5 refers to Sheet1!C7
D5 refers to Sheet1!D7
E5 refers to Sheet1!E7

Here's what I propose....

On Sheet2:
A2: 4
B2 refers to =OFFSET(Sheet1!$A$1,$A2-1,0)
B3 refers to =OFFSET(Sheet1!B$1,$A2-1,0)
C2 refers to =OFFSET(Sheet1!C$1,$A2-1,0)
D2 refers to =OFFSET(Sheet1!D$1,$A2-1,0)
E2 refers to =OFFSET(Sheet1!E$1,$A2-1,0)

Now..if you put a 7 in A5 on Sheet2, you can copy the set of formulas down
to B5 on Sheet2 and the formulas will all refer to the row 7 on Sheet1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"richy" wrote:

Yes thanks Ron.

Say my data is in A4, B4, C4, D4, E4 and I want the data from these
cells to transpose into:

a different sheet in the same file but the B2, B3, C2, D2, E2 cells, ie
the destination cells are not concurrently listed in rows or columns. I
can do the formula for a straight calculation, but cannot fatham how to
paste it to repeat in the next block of destination cells as the paste
function wants to pick the next series three cells away from the source
cell location

ie. A7, B7, C7, D7, E7 to destination worksheet B5, B6, C5, D5, E5.

When I want it to take A5, B5, C5, D5, E5 and place them in B5, B6, C5,
D5, E5.


Hope thats makes sense (it does in my head !)

Thanks for your input in advance.

Richard.