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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]$B$3[/td][td]$C$3[/td][td]$D$3[/td][td][/td][td]$B$3[/td][td]$B$4[/td][td]$B$5[/td][/tr]

[tr][td]

4​

[/td][td]$B$4[/td][td]$C$4[/td][td]$D$4[/td][td][/td][td]$C$3[/td][td]$C$4[/td][td]$C$5[/td][/tr]

[tr][td]

5​

[/td][td]$B$5[/td][td]$C$5[/td][td]$D$5[/td][td][/td][td]$D$3[/td][td]$D$4[/td][td]$D$5[/td][/tr]

[tr][td]

6​

[/td][td]$B$6[/td][td]$C$6[/td][td]$D$6[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]$B$7[/td][td]$C$7[/td][td]$D$7[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

3​

[/td][td]$B$3[/td][td]$C$3[/td][td]$D$3[/td][td][/td][td]$B$3[/td][td]$C$3[/td][td]$D$3[/td][td]$B$4[/td][td]$C$4[/td][td]$D$4[/td][td]$B$5[/td][/tr]

[tr][td]

4​

[/td][td]$B$4[/td][td]$C$4[/td][td]$D$4[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]$B$5[/td][td]$C$5[/td][td]$D$5[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F3 and copy across,

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