Cell references can be constructed programmatically using a combination of the concatenate(), text() and indirect() functions with some numeric formula. This works fine, but can make the call formula look crowded and clumsy. It also prevents the referred cell address to be automatically updated when the target cell is moved (dragged) on the spreadsheet.
For example, make a cell equal to B2:
=INDIRECT(CONCATENATE("B", TEXT(3-1,"0")))
3-1
is for illustrative purposes - the actual formula is more complicated
Is there a way to create the reference more directly, i.e. w/o going to the TEXT manipulation domain, something like:
=("B"):(3-1)
And by this also preserve the automatic referencing of a moved cell B1?