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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
<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,128,092
Messages
5,628,620
Members
416,329
Latest member
phxdan79

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