0

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.

Improve this question

2 Answers 2

Reset to default
2

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.. ( :

Improve this answer
3

You can also use index:

=INDEX(INDIRECT(B$1&"!A:ZZ"),ROW(),$W$1+1)
Improve this answer
4
  • That would allow it to go past Z.
    – mark fitzpatrick
    Commented Jun 2, 2021 at 19:33
  • This doesn't include the Sheet names.
    – p._phidot_
    Commented Jun 3, 2021 at 4:20
  • @p._phidot_ it sure does. The indirect inside uses the index allows for the dynamic sheet name.
    – Scott Craner
    Commented Jun 3, 2021 at 10:47
  • oh.. Sorry.. You are right.. I missed that.. /(-_-)
    – p._phidot_
    Commented Jun 3, 2021 at 14:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .