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
HUGE excel list of system reachability statistics. I have the data
want in one row and every seventh column. How could I import this int
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 b
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 inf
from the 55row every seventh column. Would there be a way to make
function of some sort to automate this, so I do not have to copy al
the thousands? Help?:S

Thank you so much for any help

huggs

Laistr

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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

Well I copy paste and does not accept it. If I change it to OFSZET (tha
is the same function in Hungarian = I am Hungarian so is my excel) the
it accepts BUT not with , only ; (in help I see function descriptio
using it). It is possible that some things give #name for variou
regional settings but I tried both your version and the hungaria
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 i
down it won't change the B into the +7 one but will do $B$56 :S

Laistr

 

[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!
=====================================================

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

 

[Guest]

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.