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?
3 Answers
Not sure if this helps, but I might also suggest using the OFFSET function.
OFFSET(cell reference, rows, cols [, height [, width]])e.g., you might use =OFFSET(B3,-1,0) and the cell reference will update as your data is moved around
Your example is directly equivalent to
=INDIRECT("B"&(3-1))You can use INDEX which will update if you drag it across, e.g.
=INDEX(B:B,3-1)
drag that across and it will change to C:C, D:D etc.
3
