I've almost figured out how to drag formulas but have a question

[jeremypyle]

I've almost figured out how to drag formulas. What formula can you write in order to drag a certain number to the right and also down.

For example:

a1:how
a2:do
a3:you
a4:drag
a5:this
a6:formula
a7:to
a8:the
a9:right
a10:and
a11:down

I want to enter a formula into cell b1 that will drag 4 to the right using the drag duplicate button. Then I want to drag downwards. It must enter in the following format:

b1:how
c1:do
d1:you
e1:drag
b2:this
c2:formula
d2:to
e2:the
b3:right
c3:and
d3:down

Please note. I only want to enter this formula into cell b1. The rest must be down only by dragging. I am wanting to learn exactly how to use the COLUMN and ROW function as well as numbers such as +1 or /2 etc.

Is it possible to do this with only 1 formula in cell b1 and then drag to the right and down?

Thanks so much

 

[Andrew Poulsom]

What's the formula?

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">how</td><td style="background-color: #FFFF99;;">how</td><td style=";">do</td><td style=";">you</td><td style=";">drag</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">do</td><td style=";">this</td><td style=";">formula</td><td style=";">to</td><td style=";">the</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">you</td><td style=";">right</td><td style=";">and</td><td style=";">down</td><td style="text-align: right;;">#REF!</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">drag</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">this</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">formula</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">to</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">the</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">right</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">and</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">down</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$11,(<font color="Red">(<font color="Green">ROW(<font color="Purple">A1</font>)-1</font>)*4</font>)+COLUMN(<font color="Red">A1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jeremypyle]

Thanks so much alphafrog. That has answered my question regarding columns and rows. I understand how it works now. That was exactly what I was after. Your help has been greatly appreciated.