Skip X cells when dragging a simple formula

[Lia33]

Hi Lovely all,

I'm trying to drag a formula but would like to skip two columns.
I have 2 sheets.
Sheet 1 with results and the other with column A originals, B actuals, C difference.
Sheet 2 has only originals.

So if I drag it goes: ='Sheet 1'A2, ='Sheet 1'B2, ='Sheet 1'C2, etc...
What I would like is ='Sheet 1'A2, ='Sheet 1'D2, ='Sheet 1'H2, etc...

Does anyone know how to do this without having to create a Macros?

I really hope you can help me

Many thanks

 

[Jonmo1]

Welcome to the board..

Try

=INDEX('Sheet 1'!2:2,((COLUMNS($A:A)-1)*3)+1)

Do not change COLUMNS($A:A)-1
It has nothing to do with the location of your data.

the 3 represents the interval (every 3 columns)
the +1 is the column # to begin in.


Hope that helps.

 

[T. Valko]

Hi Lovely all,

I'm trying to drag a formula but would like to skip two columns.
I have 2 sheets.
Sheet 1 with results and the other with column A originals, B actuals, C difference.
Sheet 2 has only originals.

So if I drag it goes: ='Sheet 1'A2, ='Sheet 1'B2, ='Sheet 1'C2, etc...
What I would like is ='Sheet 1'A2, ='Sheet 1'D2, ='Sheet 1'H2, etc...

Does anyone know how to do this without having to create a Macros?

I really hope you can help me

Many thanks

Try this...

Leyt's assume you want to enter the first formula in cell A1 and copy across.

=INDEX('Sheet 1'!2:2,COLUMNS($A1:A1)*3-2)

 

[Lia33]

Nope

I need to start on column F as column A has titles and even when dragging the formula it comes up with a different result as the adjacent columns.

It should come with 43.11 and comes up with 45.72....

Not sure what's the problem.

 

[Jonmo1]

Try

=INDEX('Sheet 1'!2:2,((COLUMNS($A:A)-1)*3)+6)

Welcome to the board..

Try

=INDEX('Sheet 1'!2:2,((COLUMNS($A:A)-1)*3)+1)

Do not change COLUMNS($A:A)-1
It has nothing to do with the location of your data.

the 3 represents the interval (every 3 columns)
the +1 is the column # to begin in.


Hope that helps.

 

[Lia33]

Thank you Jonmo1!!!

It does work!!

The explanation made it all happen, I just had to change the number referring to the column to start with and now it works!!

Thank you sooooo much everyone!!!

 

[Bperrem]

I know this thread is from a few years ago but I have the reverse problem and thought it easiest to use this background.

I have a sheet where I want to compare results to two new versions. The results are copied from another source which puts them in sequential columns.

I am entering the new data so copying across is not an issue I will be typing over it.

Data in source sheet starts from D10

Can this be done?