Google Sheets : How to convert cell address to text/string?

Question

I want to change the cell address to string/text. For example :

AB123 --> "AB123"

I want to use it with indirect(), for example to refer to A1:A100 where the '100' is fetched from cell X1 -->

indirect( to_string(  A1 ) & ":A" & X1  )

I didn't do indirect("A1:A"&X1) because since "A1" is static string so when i insert a new row on the very top so to shift down row 1, then the formula won't update automatically and will still refer to A1:A100 , in fact it should now refer to A2:A100 after the row insert. In this case i want to keep the A1 not as string so after insert it should updated to A2

What is the simplest way to do it?

Answer 1

Try this:

=cell("address", AB123)