copy formula for every 3rd column

boxsterman

Active Member
Joined
Apr 16, 2002
Messages
279
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
Joined
Apr 16, 2002
Messages
279
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.

Thanks for all your help.

[/img]
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 

Forum statistics

Threads
1,147,690
Messages
5,742,653
Members
423,746
Latest member
Joaogomes

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
Top