HOW TO: Increment column by7 but row stays same into new column?

[Laistra]

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

[rmellison]

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

[Laistra]

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

[rmellison]

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

[Laistra]

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

[rmellison]

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

[Laistra]

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

[RagDyeR]

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" <[email protected]> wrote in
message news:[email protected]...

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

[rmellison]

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