Dragging a formula horizontally

[amhorn17]

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!

 

[shg]

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))

 

[NeonRedSharpie]

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:

=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:

=INDIRECT("'Drop Excel Report Here'!" & A1)

That can be dragged over to the right.

 

[amhorn17]

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!!!!

 

[shg]

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)