| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Changing the Helper Column
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))) |
|
#2
|
|||
|
|||
|
Changing the Helper Column
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 |
|
#3
|
|||
|
|||
|
Changing the Helper Column
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 |
|
#4
|
|||
|
|||
|
Changing the Helper Column
=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)) 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 |
|
#5
|
|||
|
|||
|
Changing the Helper Column
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 |
|
#6
|
|||
|
|||
|
Changing the Helper Column
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 |
|
#7
|
|||
|
|||
|
Changing the Helper Column
Try these:
=IF(INDEX($5:$5,ROWS($1:3)*2)=0,"",INDEX($5:$5,ROW S($1:3)*2)) AND =IF(INDEX($5:$5,ROWS($1:3)*2+1)=0,"",INDEX($5:$5,R OWS($1:3)*2+1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "apples72" wrote in message ... 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 |
|
#8
|
|||
|
|||
|
Changing the Helper Column
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:[color=blue] 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 |
|
#9
|
|||
|
|||
|
Changing the Helper Column
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,ROW S($1:3)*2)) AND =IF(INDEX($5:$5,ROWS($1:3)*2+1)=0,"",INDEX($5:$5,R OWS($1:3)*2+1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "apples72" wrote in message ... 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 |
|
#10
|
|||
|
|||
|
Changing the Helper Column
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 |
|
#11
|
|||
|
|||
|
Changing the Helper Column
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:[color=blue] 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 |
|
#12
|
|||
|
|||
|
Changing the Helper Column
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 |
|
#13
|
|||
|
|||
|
Changing the Helper Column
Good to see, and thanks for the reply. apples72 Wrote:[color=blue] Thanks, it worked... once I realized I was missing a few brackets! "Bryan Hessey" wrote: 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 ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=484621 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
| Running total w/2 columns - Excel | Excel Worksheet Functions | |||
| up to 7 functions? | Excel Worksheet Functions | |||
| vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
| How can I sort an entire spreadsheet from a list | Excel Worksheet Functions | |||
Linear Mode
