Getting cell index from cell to the right

Hi,

I want to have a formula that retrievs the index from the cell to the right
of the cell with the formula.

I know that the formula below gives the index of the cell you are in but
that doesn't help me much unfortunately. Is there a simplier way or maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell to the right
of the cell with the formula.

I know that the formula below gives the index of the cell you are in but
that doesn't help me much unfortunately. Is there a simplier way or maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell which
will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus

"JMay" wrote:

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell to the right
of the cell with the formula.

I know that the formula below gives the index of the cell you are in but
that doesn't help me much unfortunately. Is there a simplier way or maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

Would you care to elaborate on what you intend to do with that address of
the cell on the right?

The reason I'm asking, is because you can use a "relative" named formula
that can return the *contents* of that cell to the right of which ever cell
you enter it into.

Would that be of interest to you?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Pontus" wrote in message
...
Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell
which
will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus

"JMay" wrote:

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell to the
right
of the cell with the formula.

I know that the formula below gives the index of the cell you are in
but
that doesn't help me much unfortunately. Is there a simplier way or
maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

"Pontus" wrote in message

Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell
which will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus


.... maybe: =ADDRESS(ROW();COLUMN()+1)

--
pozdrav!
Berislav

Okey to elaborate (this is gonna be a long one =P).

I'm trying to make my work of sorting a column of articles easier. Lets say
I have a column where every cell contains one article, e.g.

A B
1 Chair
2 Table
3 Apple
4 Sofa
5 Pasta


What I would like to do is to have a formula that I can copy into cell A1
and then drag down so that the cells in column A gives instead a name of a
group that the article in column B belongs to. The answer I want could for
instance be:


A B
1 Furniture Chair
2 Furniture Table
3 Food Apple
4 Furniture Sofa
5 Food Pasta

Hitherto I have managed to write the code that recognizes a word and gives
back the name of the group. My only problem now is that I somehow need to
refer to the cell that is possitioned to the right of the cell where I
copy-paste my code. This has to be done without going into the code and
change manually since the code is kind of massive. I want other persons to be
able to use this that do not know which variables to change.

If anybody has the answer how to do this it would be really great

thanx
Pontus

"Ragdyer" wrote:

Would you care to elaborate on what you intend to do with that address of
the cell on the right?

The reason I'm asking, is because you can use a "relative" named formula
that can return the *contents* of that cell to the right of which ever cell
you enter it into.

Would that be of interest to you?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Pontus" wrote in message
...
Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell
which
will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus

"JMay" wrote:

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell to the
right
of the cell with the formula.

I know that the formula below gives the index of the cell you are in
but
that doesn't help me much unfortunately. Is there a simplier way or
maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

From what you're describing, a simple Vlookup formula should work very
easily for you ... unless I'm missing something.

All you need is a datalist to create your associations.

From your example, say in J1 to J6, you enter:
ITEM, chair, table, sofa, apple, pasta,

And, in K1 to K6, you enter:
GROUP, furniture, furniture, furniture, food, food.

Then in A1, enter this formula:

=VLOOKUP(B1,$J$2:$K$6,2,0)

And copy down as needed.

That would solve your problem ... as I understand it!

What did I miss?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Pontus" wrote in message
...
Okey to elaborate (this is gonna be a long one =P).

I'm trying to make my work of sorting a column of articles easier. Lets
say
I have a column where every cell contains one article, e.g.

A B
1 Chair
2 Table
3 Apple
4 Sofa
5 Pasta


What I would like to do is to have a formula that I can copy into cell A1
and then drag down so that the cells in column A gives instead a name of a
group that the article in column B belongs to. The answer I want could for
instance be:


A B
1 Furniture Chair
2 Furniture Table
3 Food Apple
4 Furniture Sofa
5 Food Pasta

Hitherto I have managed to write the code that recognizes a word and gives
back the name of the group. My only problem now is that I somehow need to
refer to the cell that is possitioned to the right of the cell where I
copy-paste my code. This has to be done without going into the code and
change manually since the code is kind of massive. I want other persons to
be
able to use this that do not know which variables to change.

If anybody has the answer how to do this it would be really great

thanx
Pontus

"Ragdyer" wrote:

Would you care to elaborate on what you intend to do with that address
of
the cell on the right?

The reason I'm asking, is because you can use a "relative" named formula
that can return the *contents* of that cell to the right of which ever
cell
you enter it into.

Would that be of interest to you?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Pontus" wrote in message
...
Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell
which
will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus

"JMay" wrote:

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell to
the
right
of the cell with the formula.

I know that the formula below gives the index of the cell you are
in
but
that doesn't help me much unfortunately. Is there a simplier way
or
maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

Thanx Ragdyer,

Vlookup would function for me. I've been spending some time now on checking
into both V and Hlookup but have still decided to stick to my formula where I
use Match (you can see it in a picture if you follow the url below).

My current problem is of slight different kind than you understood though.
It is how to, with code, get a reference to the cell to the right of where
the code is possitioned. If you follow the url below you might understand my
problem better =)

http://www.yuki-forever.com/sp/ee.jpg


My next problem after this will be how to get the formula to function when
the table is in another sheet or document since the List to Sort will allways
be in a seperate excel document.

Thanx for all your help
Pontus


"Ragdyer" wrote:

From what you're describing, a simple Vlookup formula should work very
easily for you ... unless I'm missing something.

All you need is a datalist to create your associations.

From your example, say in J1 to J6, you enter:
ITEM, chair, table, sofa, apple, pasta,

And, in K1 to K6, you enter:
GROUP, furniture, furniture, furniture, food, food.

Then in A1, enter this formula:

=VLOOKUP(B1,$J$2:$K$6,2,0)

And copy down as needed.

That would solve your problem ... as I understand it!

What did I miss?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Pontus" wrote in message
...
Okey to elaborate (this is gonna be a long one =P).

I'm trying to make my work of sorting a column of articles easier. Lets
say
I have a column where every cell contains one article, e.g.

A B
1 Chair
2 Table
3 Apple
4 Sofa
5 Pasta


What I would like to do is to have a formula that I can copy into cell A1
and then drag down so that the cells in column A gives instead a name of a
group that the article in column B belongs to. The answer I want could for
instance be:


A B
1 Furniture Chair
2 Furniture Table
3 Food Apple
4 Furniture Sofa
5 Food Pasta

Hitherto I have managed to write the code that recognizes a word and gives
back the name of the group. My only problem now is that I somehow need to
refer to the cell that is possitioned to the right of the cell where I
copy-paste my code. This has to be done without going into the code and
change manually since the code is kind of massive. I want other persons to
be
able to use this that do not know which variables to change.

If anybody has the answer how to do this it would be really great

thanx
Pontus

"Ragdyer" wrote:

Would you care to elaborate on what you intend to do with that address
of
the cell on the right?

The reason I'm asking, is because you can use a "relative" named formula
that can return the *contents* of that cell to the right of which ever
cell
you enter it into.

Would that be of interest to you?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Pontus" wrote in message
...
Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell
which
will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus

"JMay" wrote:

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell to
the
right
of the cell with the formula.

I know that the formula below gives the index of the cell you are
in
but
that doesn't help me much unfortunately. Is there a simplier way
or
maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus

Hi b&s,

Sorry for missing your answer but thank you for your help.

That was exactely what I thought I were looking for. After having pulled my
hair now for a while though I realise that this formula gives back the index
of that cell but in text format. What I really nead is some code which gives
a reference to that cell instead so that the whole formula can get or just as
well gets the content of the cell.

If you check my last answer to Ragdyer you might understand much better what
I am trying to do.

thanks again
Pontus

"b&s" wrote:

"Pontus" wrote in message

Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any cell
which will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23 etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus


.... maybe: =ADDRESS(ROW();COLUMN()+1)

--
pozdrav!
Berislav

Since you insist on using your original procedure, after checking your
example, I see you have a couple of ways to go.

My original thought of using a "relative named formula" would work for you.

BTW, my delimiters are commas, so you'll have to replace them.

For example, say you click in E1.
Then, from the Menu Bar:
Insert Name Define

In the "Names In Workbook" box, key in a short name, say
rt
to signify "right".

Then, in the "Refers To" box, change whatever's there to:
=F1
(Which is the cell to the right of the cell you selected before you
started).
Then OK

Now, if you enter
=rt
in *any* cell, you'll display the contents of the cell to the immediate
right.

SO, your formula might look something like this:

=IF(rt=0,"",IF(ISNUMBER(MATCH(rt,B:B,0)),$B$2,IF(I SNUMBER(MATCH(rt,C:C,0)),$
C$2,IF(ISNUMBER(MATCH(rt,D,0)),$D$2,rt))))

After you define the named formula, any new sheet you create by copying the
original sheet will automatically contain that named formula.


b&s's Address formula can be made to work for you *also*.
Just wrap it in Indirect to change it from a Text return to an XL "legal"
cell reference.

=Indirect(Address(Row();Column()+1))

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1))=0,"",IF(IS NUMBER(MATCH(INDIRECT(ADDR
ESS(ROW(),COLUMN()+1)),B:B,0)),$B$2,IF(ISNUMBER(MA TCH(INDIRECT(ADDRESS(ROW()
,COLUMN()+1)),C:C,0)),$C$2,IF(ISNUMBER(MATCH(INDIR ECT(ADDRESS(ROW(),COLUMN()
+1)),D,0)),$D$2,INDIRECT(ADDRESS(ROW(),COLUMN()+ 1))))))

As far as your list being on another WB or WS, there's really no problem
there.
Just open both files, side by side, and click between them to set the
references.
That way XL will fill in the correct path for you.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pontus" wrote in message
...
Thanx Ragdyer,

Vlookup would function for me. I've been spending some time now on
checking
into both V and Hlookup but have still decided to stick to my formula
where I
use Match (you can see it in a picture if you follow the url below).

My current problem is of slight different kind than you understood though.
It is how to, with code, get a reference to the cell to the right of where
the code is possitioned. If you follow the url below you might understand
my
problem better =)

http://www.yuki-forever.com/sp/ee.jpg


My next problem after this will be how to get the formula to function when
the table is in another sheet or document since the List to Sort will
allways
be in a seperate excel document.

Thanx for all your help
Pontus


"Ragdyer" wrote:

From what you're describing, a simple Vlookup formula should work very
easily for you ... unless I'm missing something.

All you need is a datalist to create your associations.

From your example, say in J1 to J6, you enter:
ITEM, chair, table, sofa, apple, pasta,

And, in K1 to K6, you enter:
GROUP, furniture, furniture, furniture, food, food.

Then in A1, enter this formula:

=VLOOKUP(B1,$J$2:$K$6,2,0)

And copy down as needed.

That would solve your problem ... as I understand it!

What did I miss?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-


"Pontus" wrote in message
...
Okey to elaborate (this is gonna be a long one =P).

I'm trying to make my work of sorting a column of articles easier.
Lets
say
I have a column where every cell contains one article, e.g.

A B
1 Chair
2 Table
3 Apple
4 Sofa
5 Pasta


What I would like to do is to have a formula that I can copy into cell
A1
and then drag down so that the cells in column A gives instead a name
of a
group that the article in column B belongs to. The answer I want could
for
instance be:


A B
1 Furniture Chair
2 Furniture Table
3 Food Apple
4 Furniture Sofa
5 Food Pasta

Hitherto I have managed to write the code that recognizes a word and
gives
back the name of the group. My only problem now is that I somehow need
to
refer to the cell that is possitioned to the right of the cell where I
copy-paste my code. This has to be done without going into the code
and
change manually since the code is kind of massive. I want other
persons to
be
able to use this that do not know which variables to change.

If anybody has the answer how to do this it would be really great

thanx
Pontus

"Ragdyer" wrote:

Would you care to elaborate on what you intend to do with that
address
of
the cell on the right?

The reason I'm asking, is because you can use a "relative" named
formula
that can return the *contents* of that cell to the right of which
ever
cell
you enter it into.

Would that be of interest to you?
--
Regards,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-

"Pontus" wrote in message
...
Hmm, I'll try to explain better.

What I want to do is to have a formula that I can copy in to any
cell
which
will give the cell index of the cell to the right.

Exampel:
If I copy my formula in to cell K23 I want the answer to be L23 or
If I copy my formula in to cell B23 I want the answer to be C23
etc.

Hope I made myself more clear now =)

Thanks for answering.
Pontus

"JMay" wrote:

Do you mean:
A1 =Offset($B1,0,1)
B1 = Sum(A2:A4)
C1 = whatever

"Pontus" wrote:

Hi,

I want to have a formula that retrievs the index from the cell
to
the
right
of the cell with the formula.

I know that the formula below gives the index of the cell you
are
in
but
that doesn't help me much unfortunately. Is there a simplier
way
or
maybe a
way that utilises this code?

=SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"")

Best Regards
Pontus