Range("A" & (ActiveCell.Row)).Copy translated in formula

[littlepete]

Google is not helping me : i'm looking to copy the value of column Ax and any row (1~400) to another cell...
always i'm getting =Range("Ax" & (ActiveCell.Row)).value but that's for vba i need the translation into a formula...

I hope your last day of the year is fun !!!

 

[bbrnx19]

try =INDIRECT("Ax"&ROW(A1),1)

 

[littlepete]

try =INDIRECT("Ax"&ROW(A1),1)

thank you so far

I have a feeling that the row(a1),1 does not change when my selection is forex. on row 45 ?
then the content of cell Ax45 should be copied ... but it's not working... there are 400 rows the formula should
work for every row...
It's not clear what the ,1 at the end of the row function does...
so far i have :
=INDIRECT(sheets("gegevens").range("Ax"&RIJ(A5);1))

thank you !!!

 

[bbrnx19]

the two formula take the from of INDIRECT(ref_text,[a1]) and ROW(reference) .
ROW(A1) = 1
ROW(A2) = 2 autofill or drag down cell

ROW() = row number of current cell this formula is in

INDIRECT(ref_text,[a1])

ref_text = text string of the formula "'sheet1'!A1" [a1] = 1 address as A1 or 0 address as R1C1

=INDIRECT("'gegevens'!Ax"&ROW();1) placed in any cells row 1~400

 

[MARK858]

Hi Pete, AFAIK you can't get the address or row/column number of the ActiveCell with a formula like you can with VBA, you would have to click in the formula and then click the cell you want it referencing.

You can get the row number of the cell the formula resides in if that is what you are aiming to use but not the row number of the Activecell if it is not the same as the formulas row or it would need to be a fixed cell.

The 1 (or TRUE) just tells INDIRECT that the cell being referenced is in A1 reference style (it can be omitted as TRUE is the default)

 

[littlepete]

let me explain what the result should be:

i have a list of names, each person is a row. column Q is the first name; column R is the last name.
column Ax is the name of the mother; column Ay is the name of the father.

for each person (400 rows) i would like to appear the name of the mother in cell B2
and the father in cellC2.

the rows 1-4 are fixed and don't move...
is it possible to make that "link" ?

 

[MARK858]

Are you saying that you are dragging down the formulas in B2 and C2?
Can you post using XL2BB the first 15 rows of your data, including the result you desire manually created by you please.

 

[littlepete]

Good afternoon

my idea is to fill the cells B2 en C2 each time another row (person) is selected...
that would give the possibility when a person is selected, to click one of those two cells,
and end up on the row of that person's mother or father...

on the picture you will see the two first (fake) test persons...
in row 2 in yellow should appear links to mother and father (b2 and c2).

the green row 3 are cells to start macros (c to f) ...

the "real" data are in the columns K to Ck ...

thanks for your time, and have fun in 2022 !!!

 

[MARK858]

my idea is to fill the cells B2 en C2 each time another row (person) is selected...

I am afraid that I can't see how that can be achieved purely with a formula without (as previously mentioned) clicking within the formula before each person is selected, AFAIK a formula cannot detect a randomly selected cell.
You would have to use VBA either as an event code, regular code or as an UDF.

 

[littlepete]

I will try to think of another way ... it's nice when everything works well thank you for the explanation !!!