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

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
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:)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
<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
Joined
May 30, 2011
Messages
166
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,414
Messages
5,596,001
Members
414,037
Latest member
Roamingsmile

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top