Dragging a formula horizontally

amhorn17

New Member
Joined
Oct 22, 2014
Messages
2
Hello! I need some help on how to drag a formula horizontally. I need the columns to remain the same but the row to increase by 1.

I am referencing a different tab in the same workbook for the data. My initial formula looks like this:

='Drop Excel Report Here'!B3
='Drop Excel Report Here'!C3
='Drop Excel Report Here'!D3

I need this pattern to repeat but the cells to change to B4, C4, D4, B5, C5, D5, etc. as it is dragged across. Any ideas?

Thank you!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Welcome to the board.

Row\Col
B​
C​
D​
E​
F​
G​
H​
1​
2​
3​
$B$3$C$3$D$3$B$3$B$4$B$5
4​
$B$4$C$4$D$4$C$3$C$4$C$5
5​
$B$5$C$5$D$5$D$3$D$4$D$5
6​
$B$6$C$6$D$6
7​
$B$7$C$7$D$7

In F3 and copy down and right,

=INDEX($B:$D, COLUMNS($F$3:F3) + 2, ROWS($F$3:F3))
 
Upvote 0
Use an indirect function with a helper row. In your helper row (let's assume it starts at A1 and we're using row 1) place B3. Then, in B1, place:

Code:
=IF(LEFT(A1)="B","C" & RIGHT(A1),IF(LEFT(A1)="C","D" &RIGHT(A1),"B" & (RIGHT(A1)+1)))

You can drag that over to the right as far as you want.

So your formula will now be:
Code:
=INDIRECT("'Drop Excel Report Here'!" & A1)

That can be dragged over to the right.
 
Upvote 0
Thank you for the quick reply! I think the way I put my formula/pattern in my post confused what I am looking for. The formulas I have listed above are actually in cells horizontally - not listed vertically as I have listed. So: ='Drop Excel Report Here'!B3 ='Drop Excel Report Here'!C3 ='Drop Excel Report Here'!D3, etc. I need this to repeat horizontally w/ the row increasing every 3. Am I making any sense or do I just not understand how your solution applies for me?? Thank you!!!!
 
Upvote 0
Row\Col
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
3​
$B$3$C$3$D$3$B$3$C$3$D$3$B$4$C$4$D$4$B$5
4​
$B$4$C$4$D$4
5​
$B$5$C$5$D$5

In F3 and copy across,

=INDEX($B:$D, INT((COLUMNS($F$3:F3) - 1)/3) + 3, MOD(COLUMNS($F$3:F3) - 1, 3) + 1)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top