Get Cell Reference From Cell Address ?

[Jaafar Tribak]

Hi dear forum members,

Is there a formula that would take a cell address and return the corresponding cell reference so I can then use this reference as an argument in other fonctions such as INDEX etc... ?

Looking for a Non-UDF solution.

Regards.

 

[FormR]

Hi Jaafar,

Take a look at INDIRECT() - for example.

Excel 2013/2016
	A	B	C
1	A	A1:A100	C
2	B
3	C
4	D
5	E
6	F
Sheet1

Cell Formulas
Range	Formula
C1	=INDEX(INDIRECT(B1),3)

 

[Jaafar Tribak]

Hi Jaafar,

Take a look at INDIRECT() - for example.

Thanks for the quick response FormR,

The INDIRECT function takes a cell reference not a cell address.

I need to pass a literal address string something like "B1".

 

[FormR]

The INDIRECT function takes a cell reference not a cell address

Hi Jaafar, no - it takes a string and returns a range reference.

In my example I put the string in a cell, but you don't need to do that, for example.

Excel 2013/2016
	A	B
1	A	C
2	B
3	C
4	D
5	E
6	F
Sheet1

Cell Formulas
Range	Formula
B1	=INDEX(INDIRECT("A1:A100"),3)

 

[Jaafar Tribak]

You are right.

The cell reference resolves to a string in the end... I am trying to incorporate the INDIRECT(), ADDRESS() AND CELL("address") functions into a larger formula... I'll get back if I get stuck.

Thank you very much for your help.