Changing the Helper Column

apples72 · 11-13-2005, 02:50 AM

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** · 11-13-2005, 05:04 AM

=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))

Originally Posted by apples72

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)))

apples72 · 11-13-2005, 05:10 AM

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" wrote:

>
> If I have understood correctly, column AA to have A1, C1, E1 etc and the
> 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)
>
>
>
> apples72 Wrote:
> > 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
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>

**Bryan Hessey** · 11-13-2005, 05:26 AM

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

To start at AA5 use

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

and

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

Originally Posted by apples72

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" wrote:

>
> If I have understood correctly, column AA to have A1, C1, E1 etc and the
> 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)
>
>
>
> apples72 Wrote:
> > 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
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>

apples72 · 11-13-2005, 05:55 AM

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.

"Bryan Hessey" wrote:

>
> 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
>
>
>
> apples72 Wrote:
> > 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" wrote:
> >
> > >
> > > If I have understood correctly, column AA to have A1, C1, E1 etc and
> > the
> > > 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)
> > >
> > >
> > >
> > > apples72 Wrote:
> > > > 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
> > >
> > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> > http://www.excelforum.com/member.php...o&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=484621
> > >
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>

Ragdyer · 11-13-2005, 06:00 AM

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))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"apples72" <apples72@discussions.microsoft.com> wrote in message
news:1A071997-2A29-4D3C-874B-C69ECEB6ACE0@microsoft.com...
> 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" wrote:
>
> >
> > If I have understood correctly, column AA to have A1, C1, E1 etc and the
> > 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)
> >
> >
> >
> > apples72 Wrote:
> > > 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
> > ------------------------------------------------------------------------
> > Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
> > View this thread:
http://www.excelforum.com/showthread...hreadid=484621
> >
> >

**Bryan Hessey** · 11-13-2005, 06:11 AM

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

[QUOTE=apples72]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" wrote:
[color=blue]

apples72 · 11-13-2005, 06:35 AM

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

"Ragdyer" wrote:

> 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))
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "apples72" <apples72@discussions.microsoft.com> wrote in message
> news:1A071997-2A29-4D3C-874B-C69ECEB6ACE0@microsoft.com...
> > 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" wrote:
> >
> > >
> > > If I have understood correctly, column AA to have A1, C1, E1 etc and the
> > > 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)
> > >
> > >
> > >
> > > apples72 Wrote:
> > > > 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
> > > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> http://www.excelforum.com/member.php...o&userid=21059
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=484621
> > >
> > >
>
>

apples72 · 11-13-2005, 06:35 AM

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

"Bryan Hessey" wrote:
[color=blue]
>
> 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
>
>
>
> apples72 Wrote:
> > 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" wrote:
> >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>

**Bryan Hessey** · 11-13-2005, 06:38 AM

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.

ADDED

If you still have trouble, copy this into AA5 to test,

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

and then double that into your IF statement

Good Luck

Originally Posted by apples72

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

"Bryan Hessey" wrote:
[color=blue]
>
> 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
>
>
>
> apples72 Wrote:
> > 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" wrote:
> >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>

apples72 · 11-13-2005, 08:25 AM

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

"Bryan Hessey" wrote:
[color=blue]
>
> 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.
>
>
>
> apples72 Wrote:
> > I am now getting the error message, " you have entered too many
> > arguements
> > forthis function"
> >
> > "Bryan Hessey" wrote:
> >
> > >
> > > 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
> > >
> > >
> > >
> > > apples72 Wrote:
> > > > 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" wrote:
> > > >
> > >
> > >
> > > --
> > > Bryan Hessey
> > >
> > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> > http://www.excelforum.com/member.php...o&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=484621
> > >
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>

**Bryan Hessey** · 11-13-2005, 06:22 PM

Good to see, and thanks for the reply.

ps, it may be better to highlight and Copy from these posts, then select the cell and Paste the formula, it avoids typing errors. Where the formula as copied doesn't 'take' remove just the equal (=) sign, re-type the = sign and then press Enter, it should work.

I also detected an error in the formula, it started at F1 and had 27 as a row adjuster (the reason for which I couldn't work out last night).
It should (of course) start from F5 and would then be:

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

which can be formula-dragged to AB5 (having removed the $ from $F)

Cheers

Originally Posted by apples72

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

"Bryan Hessey" wrote:
[color=blue]
>
> 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.
>
>
>
> apples72 Wrote:
> > I am now getting the error message, " you have entered too many
> > arguements
> > forthis function"
> >
> > "Bryan Hessey" wrote:
> >
> > >
> > > 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
> > >
> > >
> > >
> > > apples72 Wrote:
> > > > 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" wrote:
> > > >
> > >
> > >
> > > --
> > > Bryan Hessey
> > >
> > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> > http://www.excelforum.com/member.php...o&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=484621
> > >
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=484621
>
>