Transposing formula 1

[mtdew]

I have a formula that I copy and paste down the A column:

="Q:"&Sheet1!A30
="A:"&Sheet1!B30
="A:"&Sheet1!C30
="A:"&Sheet1!D30
="A:"&Sheet1!E30

But when I start the copy process over again the next time I paste the row will have changed to 35 instead of 30. How can I make it change to 31 instead of 35? (Q = "Question:" and A = "Answer:")

 

[macropod]

Hi mtdew,

You could use a formula like:
="A:"&OFFSET(Sheet1!$A$30,INT((ROW()-1)/5),MOD(ROW()-1,5))
in the first cell and make both '1' values whatever your formula's starting row number is. Then copy down as far as needed.


Cheers
[MS MVP - Word]