Copy Formula Horizontally, Incrementing Row

[waxsublime]

I'm trying to figure out how to automatically increment the row reference in a formula copied horizontally.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]='Example Tab'!$A2
[/TD]
[TD]='Example Tab'!$A3
[/TD]
[TD]='Example Tab'!$A4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

...and so on across several columns.

How can I do that?

Thanks in advance!

 

[pgc01]

Hi
Welcome to the board

For ex., if the first formula is in C3:

=INDEX('Example Tab'!$A$2:$A$1000,COLUMNS(C$3:C3))

Copy across

If necessary change the maximum row.

 

[markmzz]

Maybe this:

=INDIRECT("'Example Tab'!$A"&COLUMNS($A:B))

Markmzz

 

[waxsublime]

Thanks for the replies! Unfortunately neither of those worked. However, I think I found an easy solution.

1. AutoCopy/fill the formula down (instead of right) (e.g. starting at A1)
2. Copy those cells to clipboard
3. Paste clipboard into adjacent right cell (e.g. B1)
4. Transpose the Pasted formula contents
5. Cut those pasted cells and paste into original cell (e.g. A1)

 

[markmzz]

Thanks for the replies! Unfortunately neither of those worked.

Could you post the formula that you used?

Here it work, I put the formula in A1 (in sheet1) and copy to B1:E1.

No problem.

Markmzz

 

[guibgirl86]

this is what I use. If you have a formula in one cell and want to copy it down the column you can use this.

Range("N2").Formula = "=K2-M2"
    Range("N2").Select
        Selection.AutoFill Destination:=Range("N2:N" & n), Type:=x1filldefault

n is a count of cells down the column, I set this number I calculated earlier in my code. You can use this or just specify the end of the range that you want.

to copy across i would think that you would set the range destination as Range("N2:G2").

 

[waxsublime]

Markmzz, You're absolutely right. I'm not sure what I was doing wrong.

Both your INDIRECT and the INDEX formulas work to accomplish what I was doing. However, the INDEX Formula above needed to be corrected to:

=INDEX('Example Tab'!$A:$A,COLUMNS($A$2:A2))

That is with info on the Example Tab beginning at A1.

Thanks again for your replies.

 

[waxsublime]

Actually, to make the Index Formula more simple, and sum everything up...

=INDEX('Example Tab'!$A:$A,COLUMNS($A:B))

Indirect...

=INDIRECT("'Example Tab'!$A"&COLUMNS($A:B))

Both above work.

On Example Tab:

On Active Worksheet:

The "&Columns($A:C)" part returns the number of columns from Col A to Col C (which is 3). The result is that the formula becomes: "Example Tab A3"

Very cool stuff. Thanks again for the help all!

 

[waxsublime]

Okay, I have another question actually.

So the above works for incrementing row in a simple formula. But what if it's a complex IF formula like this:

=IF(OR((AND(OR(ISNUMBER(FIND(Mapping!$B$3,$D2)),ISNUMBER(FIND(Mapping!$D$3,$F2))),$E2=Mapping!$E$3)),$M2=Mapping!$A$3),1,)

Say this is the formula I need in Column R. But I want to copy it horizontally incrementing the row on the "Mapping" tab

e.g. in Column S, it should be:

=IF(OR((AND(OR(ISNUMBER(FIND(Mapping!$B$4,$D2)),ISNUMBER(FIND(Mapping!$D$4,$F2))),$E2=Mapping!$E$4)),$M2=Mapping!$A$4),1,)

Is there a way to get INDIRECT or INDEX to work for that? I can't seem to figure that out.

 

[markmzz]

Try this small modification in your formula:

=IF(OR((AND(OR(ISNUMBER(FIND(INDIRECT("'Mapping'!$B"&COLUMNS($R:T)),$D2)),
ISNUMBER(FIND(INDIRECT("'Mapping'!$D"&COLUMNS($R:T)),$F2))),$E2=INDIRECT("'Mapping'!$E"&COLUMNS($R:T)))),
$M2=INDIRECT("'Mapping'!$A"&COLUMNS($R:T))),1,)

Markmzz