# copy formula for every 3rd column

#### boxsterman

##### Active Member
Hi all... I have a sheet, columns are for each month (bi monthly). I have this data pulling directly from another page in which upto 3 potential columns could be used for info. i.e. column A on sheet2, required data from columns A,B,C on Sheet1. How do I copy the formula from Column A on sheet 2, so that columnB pulls info from the appropriate column (E,D,F) on sheet1...

I basically need it to copy column A + 3 columns.

Thanks.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### parry

##### MrExcel MVP
Hi, heres an example using Index to reference a cell and Column to determine what column your on at the moment. To go every three columns multiply column by 3.
Book1
ABCDEFGHI
1123456789
2369
Sheet1

#### boxsterman

##### Active Member
Thanks... but not exactly what I need....

Here's the deal... cell A1 references cell A1 (on another sheet). I want cell B1 to reference cell D1 (on the other sheet), and cell C1 to reference cell G1 (on the other sheet) etc... etc....

I need a formula to copy and past that can handle this... its easy enough to reference rows this way... why not columns.

[/img]

#### parry

##### MrExcel MVP
Hi again, you never gave the sheet or cell references so I couldnt give you an exact example. ie the more info you give the more precise the answer

OK, you just have to adjust the reference so that the column is adjusted based upon where the formula is in your sheet against the column of the target sheet.

eg presuming your entering the formula in cell A1 and the target sheet is called Sheet1 then this should do it.

=INDEX(Sheet1!1:1,1,(COLUMN()*3)-2)

A1 is the cell where the formula is contained so this is column 1. In the target sheet you want to go every 3 columns so that means you need to multiply the column where the formula is located by 3 (so 3 * 1 = 3). Now the target column is actually A, so you need to deduct 2 from this result.

hth

Replies
4
Views
111
Replies
3
Views
104
Replies
4
Views
137
Replies
1
Views
267
Replies
0
Views
249

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,826
Messages
5,766,669
Members
425,367
Latest member
Boboka

### 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.

### Which adblocker are you using?

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

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