| 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 |
|
#7
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new column?
Well I copy paste and does not accept it. If I change it to OFSZET (that is the same function in Hungarian = I am Hungarian so is my excel) then it accepts BUT not with , only ; (in help I see function description using it). It is possible that some things give #name for various regional settings but I tried both your version and the hungarian equivalents and still gives #name:S I dunno why. Putting OFSZET($B$55) into X3 already gives me the value of $B$55 np.. if I do.. ahmm OFSZET($B$55;0;7) that also works BUT.. if I copy it down it won't change the B into the +7 one but will do $B$56 :S Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#8
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new column?
All I can do is suggest an American version solution.
Revise as necessary: =INDEX($55:$55,6*ROWS($A$1:A2)-10) And copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Laistra" wrote in message ... Well I copy paste and does not accept it. If I change it to OFSZET (that is the same function in Hungarian = I am Hungarian so is my excel) then it accepts BUT not with , only ; (in help I see function description using it). It is possible that some things give #name for various regional settings but I tried both your version and the hungarian equivalents and still gives #name:S I dunno why. Putting OFSZET($B$55) into X3 already gives me the value of $B$55 np.. if I do.. ahmm OFSZET($B$55;0;7) that also works BUT.. if I copy it down it won't change the B into the +7 one but will do $B$56 :S Laistra -- Laistra ------------------------------------------------------------------------ Laistra's Profile: http://www.excelforum.com/member.php...o&userid=28731 View this thread: http://www.excelforum.com/showthread...hreadid=484215 |
|
#9
|
|||
|
|||
|
HOW TO: Increment column by7 but row stays same into new colum
I'm not really sure what else to suggest for this one. The dollar ($) signs
in the cell reference should make it an absolute reference (apologies if you know all this already), so that if you copy the cell down to the one below, the row number remains the same. XL should not change the reference to $B$56 if you copy down to the cell below. Perhaps the hungarian version uses a different symbol to indicate an absolute reference?? Try typing the cell reference without the $ symbol and then pressing F4 to change it to an absolute reference. Does it use the same symbol? Other than that you could try using the INDIRECT function to fix the reference at B55. Instead of $B$55 in your formula, use INDIRECT("B55"). This fixes the reference as a character string and then INDIRECT makes it into a reference. Hope you get it working. "Laistra" wrote: Well I copy paste and does not accept it. If I change it to OFSZET (that is the same function in Hungarian = I am Hungarian so is my excel) then it accepts BUT not with , only ; (in help I see function description using it). It is possible that some things give #name for various regional settings but I tried both your version and the hungarian equivalents and still gives #name:S I dunno why. Putting OFSZET($B$55) into X3 already gives me the value of $B$55 np.. if I do.. ahmm OFSZET($B$55;0;7) that also works BUT.. if I copy it down it won't change the B into the +7 one but will do $B$56 :S 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 | |||
Linear Mode
