Changing the Helper Column

[Guest]

I have a worksheet that is 16 columns long, and 40 rows down, and in every 2
columns, there are 2 items that relate to the next 2, etc.

e.g. A1, C1, E1, G1, I1, K1, M1, O1 are the one related item,
B1, D1, F1, H1, J1, L1, N1, P1 are another related item, and so on.

Is there a way to modify this Column Helper so that it will enter every
other column, say AA1:300, and then use it again in another column, say
AB1:300

=IF(OFFSET(A$1,INT((ROWS($1:1)-1)/16),MOD(ROWS($1:1)-1,16))=0,"",OFFSET(A$1,INT((ROWS($1:1)-1)/16),MOD(ROWS($1:1)-1,16)))

 

[Bryan Hessey]

If I have understood correctly, column AA to have A1, C1, E1 etc and th
other B1, D1, F1 onwards, try

=OFFSET($A$1,INT((ROW()-1)/8),(MOD((ROW()-1)/8,8)-INT((ROW()-1)/8))*16)

and

=OFFSET($B$1,INT((ROW()-1)/8),(MOD((ROW()-1)/8,8)-INT((ROW()-1)/8))*16)

 

[Guest]

I am getting an error in the formula... does it matter that I am actually
starting in cell F5 and G5.

That way, cell AA5=F5, AA6=H5
and AB5=G5, AB6=I5, etc....

 

[Bryan Hessey]

=IF(OFFSET($A$1,INT((ROW()-1)/8),(MOD((ROW()-1)/8,1))*16)=0,"",OFFSET($A$1,INT((ROW()-1)/8),(MOD((ROW()-1)/8,1))*16))

and

=IF(OFFSET($B$1,INT((ROW()-1)/8),(MOD((ROW()-1)/8,1))*16)=0,"",OFFSET($B$1,INT((ROW()-1)/8),(MOD((ROW()-1)/8,1))*16))

 

[Bryan Hessey]

The formula was setup for AA1, if you start in AA1 abd AB1 and drag down
from there it should be ok
Otherwise the column offset will be in error

 

[Guest]

Still getting an error.

I am starting in AA1, and am entering the formula as typed, and I get an
"error in formula".

The first information RC is F5 which I would like to be in AA1 and G5 which
I would like to be in AB1.

Sorry for being a pain.

 

[Ragdyer]

Try these:

=IF(INDEX($5:$5,ROWS($1:3)*2)=0,"",INDEX($5:$5,ROWS($1:3)*2))

AND

=IF(INDEX($5:$5,ROWS($1:3)*2+1)=0,"",INDEX($5:$5,ROWS($1:3)*2+1))

 

[Bryan Hessey]

To start in F5 set the initial to that, in AA5 copy / paste the formula

=IF(OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

and in AB5 copy / paste

=IF(OFFSET($G$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($G$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

and formula-drag downwards

I am getting an error in the formula... does it matter that I am
actually
starting in cell F5 and G5.

That way, cell AA5=F5, AA6=H5
and AB5=G5, AB6=I5, etc....


:

 

[Guest]

This works, however once I am at U5, I need to go to the next row, and start
again at F6.

 

[Guest]

I am now getting the error message, " you have entered too many arguements
forthis function"

To start in F5 set the initial to that, in AA5 copy / paste the formula

=IF(OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

and in AB5 copy / paste

=IF(OFFSET($G$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($G$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

and formula-drag downwards

 

[Bryan Hessey]

the formula is copied from a working cell AA5 and is

=IF(OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

watch for spaces etc in the post, but it does work.

I am now getting the error message, " you have entered too many
arguements
forthis function"

 

[Guest]

Thanks, it worked... once I realized I was missing a few brackets!

the formula is copied from a working cell AA5 and is

=IF(OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

watch for spaces etc in the post, but it does work.

 

[Bryan Hessey]

Good to see, and thanks for the reply.

Thanks, it worked... once I realized I was missing a few brackets!

the formula is copied from a working cell AA5 and is

=IF(OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

watch for spaces etc in the post, but it does work.

I am now getting the error message, " you have entered too many
arguements
forthis function"

:


To start in F5 set the initial to that, in AA5 copy / paste the
formula

=IF(OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($F$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))

and in AB5 copy / paste

=IF(OFFSET($G$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16)=0,"",OFFSET($G$1,INT((ROW()+27)/8),(MOD((ROW()-5)/8,1))*16))