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

#### jeremypyle

##### Board Regular
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Andrew Poulsom

##### MrExcel MVP
What's the formula?

#### AlphaFrog

##### MrExcel MVP
<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

##### Board Regular
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.

Replies
5
Views
256
Replies
4
Views
123
Replies
4
Views
88
Replies
3
Views
298
Replies
4
Views
501

### Forum statistics

1,126,928
Messages
5,621,638
Members
415,849
Latest member
PhoenixRising2015

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back