| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
HOW TO: Increment column by7 but row stays same into new column?
Hello! Could you guys help me with a small problem I face at work. We got a HUGE excel list of system reachability statistics. I have the data I want in one row and every seventh column. How could I import this into a new column under each other? To try to give a simpe example: my data is in B55 H55 N55 etc etc.. always +7 i would like to see it (by some formule so I do not have to fill in by hand till the thousands of columns end) this way: let X3 be B55 let X4 be H55 let X5 be N55 let X6 be T55 same way my new column increasing by one in row, and getting the info from the 55row every seventh column. Would there be a way to make a function of some sort to automate this, so I do not have to copy all the thousands? Help?:S Thank you so much for any help huggs Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#2
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new column?
Try this in X3 and copy down.
=OFFSET($B$55,0,7*(ROW()-ROW($X$3)) Its a bit ugly, but it works! You could make it more robust by linking it to a cell to get the required column increment. HTH "Laistra" wrote: Hello! Could you guys help me with a small problem I face at work. We got a HUGE excel list of system reachability statistics. I have the data I want in one row and every seventh column. How could I import this into a new column under each other? To try to give a simpe example: my data is in B55 H55 N55 etc etc.. always +7 i would like to see it (by some formule so I do not have to fill in by hand till the thousands of columns end) this way: let X3 be B55 let X4 be H55 let X5 be N55 let X6 be T55 same way my new column increasing by one in row, and getting the info from the 55row every seventh column. Would there be a way to make a function of some sort to automate this, so I do not have to copy all the thousands? Help?:S Thank you so much for any help huggs Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#3
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new column?
Typed it into X3 and it says the function has faults: $B$55,0,7 In that area, and refuses to accept it keeping that part highlighted. Excel 2003 :S Thanx for trying to help:) Huggs Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#4
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new colum
My fault, the formula was missing a parenthesis. Fixed that and it worked
fine for me... =OFFSET($B$55,0,7*(ROW()-ROW($X$3))) HTH "Laistra" wrote: Typed it into X3 and it says the function has faults: $B$55,0,7 In that area, and refuses to accept it keeping that part highlighted. Excel 2003 :S Thanx for trying to help:) Huggs Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#5
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new column?
Nope still not works. Firstly if I not type ; in stead of , it never accepts the value into the field at all: =OFSZET($B$55;0;7*(ROW()-ROW($X$3))) I guess for it being due to not being English myself.. perhaps. Different separators? But when i did that it still gives #name, checking how come it refers to arrows existing already, circular or recursive linking. You said yours works, could you send a working excel sheet where this function does the wonder to me perhaps? So I can check how come it does not implement it in mine? Thanx Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#6
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new colum
#NAME error is because XL does not recognise the function. Make sure there
are no typing errors in your cell, the function name should be OFFSET. Don't know very much about effect of using different seperators either. "Laistra" wrote: Nope still not works. Firstly if I not type ; in stead of , it never accepts the value into the field at all: =OFSZET($B$55;0;7*(ROW()-ROW($X$3))) I guess for it being due to not being English myself.. perhaps. Different separators? But when i did that it still gives #name, checking how come it refers to arrows existing already, circular or recursive linking. You said yours works, could you send a working excel sheet where this function does the wonder to me perhaps? So I can check how come it does not implement it in mine? Thanx Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Row reference increment but preserve column reference | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| How do I increment or decrement values in a column? | New Users to Excel | |||
Hybrid Mode
