If you look at the following table in Column A I have some values that I want to use to make an address reference. I can successfully accomplish this with the formula in Column C copied down into each cell. I would like to use the more dynamic ArrayFormula so that when even I enter a new row it will populate with the correct Address. However, it's injecting ''! before my addresses. I don't know what to do to get rid of it.
I have a workaround in another column in row 3 I enter this. =ARRAYFORMULA(IF(LEN(B3:B), MID(B3:B,4,5),)) however I feel the address should just work without it. Anyone know why it does the unnecessary injection ''!.
+------------+---------------------------------------------------------------+-----------------------------+
| | Incorrect but dynamic | Correct if but not dynamic |
+------------+---------------------------------------------------------------+-----------------------------+
| Formula -> | ARRAYFORMULA(IF(LEN(A3:A), ADDRESS(Row(A3:A),A3:A,4,TRUE,),)) | ADDRESS(Row(A3),A3,4,TRUE,) |
| 1 | ''!A3 | A3 |
| 2 | ''!B4 | B4 |
| 3 | ''!C5 | C5 |
| 4 | ''!D6 | D6 |
| 5 | ''!E7 | E7 |
| 1 | ''!A8 | A8 |
+------------+---------------------------------------------------------------+-----------------------------+
