How to convert R1C1 string into A1 string inside excel using other formulas (no VB)?

So the thing that I use to evaluate excel files is called Apache POI and it does not know how to work with R1C1 formulas. I got a document with formulas like this:

=INDIRECT(B$1&"!R"&ROW()&"C"&($W$1+1);0)*$F$4

I can wrap each INDIRECT string content (meaning B$1&"!R"&ROW()&"C"&($W$1+1);0) into something like another excel formula or expression.

I wonder how to write something like FORMULA_R_to_A(ScarryR1C1Expression) that would convert R1C1 string into A1 string format using only excel formulas?

There are options on how one could convert R1C1 into A1. Yet here we do not get pure R1C1 as an exact path but as a complex formula that in itself firstly evaluates into a string that represents R1C1.

Solution 1:

Improved answer :

=ADDRESS(ROW(),$W$1+1,1,1,B$1)

This is how you may "convert R1C1 string into A1 string format". ( :

Previous answer (for record) :

Assuming you are only working with A-Z columns,

=INDIRECT(B$1&"!R"&ROW()&"C"&($W$1+1);0)*$F$4

should be equivalent to

=INDIRECT(B$1&"!"&CHAR(64+($W$1+1))&ROW();1)*$F$4 (thanks @mark fitzpatrick for the suggestd edit)

Idea : use CHAR() to 'generate the A1 string format.

please share if it works/not.. ( :

Solution 2:

You can also use index:

=INDEX(INDIRECT(B$1&"!A:ZZ"),ROW(),$W$1+1)