I am trying to create a formula in a given cell that would refer to a
variable cell depending on the row number of the current active cell in the
worksheet. How do I do that?
Many thanks in advance,

See Excel help on the OFFSET function

--
Biff
Microsoft Excel MVP


"JFA" wrote in message
...
I am trying to create a formula in a given cell that would refer to a
variable cell depending on the row number of the current active cell in
the
worksheet. How do I do that?
Many thanks in advance,

I am not sure this is what I was looking for. In the mean time, I found the
function CELL("Row") which seem to do just what I need, if I use it with
INDIRECT and CONCATENATE.

Now, I have another problem: once I succeed to display that variable
content, I would like to be able to edit it, and to have the updated value
reflected in its location of origin. It's clear that a simple "link" will
not work, since one of the 2 ends is variable. Any idea?

Thank so much!

"T. Valko" wrote:

See Excel help on the OFFSET function

--
Biff
Microsoft Excel MVP


"JFA" wrote in message
...
I am trying to create a formula in a given cell that would refer to a
variable cell depending on the row number of the current active cell in
the
worksheet. How do I do that?
Many thanks in advance,

Is this what you're trying to do:

Find a value somewhere then change that value at it's location?

If that's what you're trying to do then you'll need VBA code for that.

I suggest you post in the programming forum.

--
Biff
Microsoft Excel MVP


"JFA" wrote in message
...
I am not sure this is what I was looking for. In the mean time, I found
the
function CELL("Row") which seem to do just what I need, if I use it with
INDIRECT and CONCATENATE.

Now, I have another problem: once I succeed to display that variable
content, I would like to be able to edit it, and to have the updated value
reflected in its location of origin. It's clear that a simple "link" will
not work, since one of the 2 ends is variable. Any idea?

Thank so much!

"T. Valko" wrote:

See Excel help on the OFFSET function

--
Biff
Microsoft Excel MVP


"JFA" wrote in message
...
I am trying to create a formula in a given cell that would refer to a
variable cell depending on the row number of the current active cell in
the
worksheet. How do I do that?
Many thanks in advance,

Hi,

It sounded in your first post that you wanted a VBA solution, that's what
Biff was directing you towards, however, based on your response it sounds
like you wanted a spreadsheet solution. For that CELL and OFFSET and other
functions may work.

BUT, to change the cell, you will either need to do it manually or to use
VBA, at least it sounds that way. To be sure give us a concrete example,
with cell addresses, formulas, and what you want to change (which cells) and
how (what you want in them), and what they have in them before they are to be
changed.


--
Thanks,
Shane Devenshire


"JFA" wrote:

I am not sure this is what I was looking for. In the mean time, I found the
function CELL("Row") which seem to do just what I need, if I use it with
INDIRECT and CONCATENATE.

Now, I have another problem: once I succeed to display that variable
content, I would like to be able to edit it, and to have the updated value
reflected in its location of origin. It's clear that a simple "link" will
not work, since one of the 2 ends is variable. Any idea?

Thank so much!

"T. Valko" wrote:

See Excel help on the OFFSET function

--
Biff
Microsoft Excel MVP


"JFA" wrote in message
...
I am trying to create a formula in a given cell that would refer to a
variable cell depending on the row number of the current active cell in
the
worksheet. How do I do that?
Many thanks in advance,

Thanks to both of you, T. & Shane: it does indeed look like I need a full
subroutine which would be triggered on the change of value of the recipient
cell and would do the reverse process to locate the mother-cell and update
the content. I am going to post that question on the programming forum.
Thanks again!

"ShaneDevenshire" wrote:

Hi,

It sounded in your first post that you wanted a VBA solution, that's what
Biff was directing you towards, however, based on your response it sounds
like you wanted a spreadsheet solution. For that CELL and OFFSET and other
functions may work.

BUT, to change the cell, you will either need to do it manually or to use
VBA, at least it sounds that way. To be sure give us a concrete example,
with cell addresses, formulas, and what you want to change (which cells) and
how (what you want in them), and what they have in them before they are to be
changed.


--
Thanks,
Shane Devenshire


"JFA" wrote:

I am not sure this is what I was looking for. In the mean time, I found the
function CELL("Row") which seem to do just what I need, if I use it with
INDIRECT and CONCATENATE.

Now, I have another problem: once I succeed to display that variable
content, I would like to be able to edit it, and to have the updated value
reflected in its location of origin. It's clear that a simple "link" will
not work, since one of the 2 ends is variable. Any idea?

Thank so much!

"T. Valko" wrote:

See Excel help on the OFFSET function

--
Biff
Microsoft Excel MVP


"JFA" wrote in message
...
I am trying to create a formula in a given cell that would refer to a
variable cell depending on the row number of the current active cell in
the
worksheet. How do I do that?
Many thanks in advance,