using the active cell as a reference in a function

[JFA]

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,

 

[JFA]

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]

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.

 

[ShaneDevenshire]

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.

 

[JFA]

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!