4

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 3

3

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

1
  • Thanks, seems to do the trick. Replace rows and cols with equivalent formulae which evaluate to 0 for the original location, and the ref target is now movable.
    – ysap
    Mar 31, 2014 at 8:25
7

Your example is directly equivalent to

=INDIRECT("B"&(3-1))
2
  • Although improving readability, this merely replaces the function calls with operators. So, it answers half of the problem, b/c if I move cell B2 to C2, then I have to explicitly change the "B" part of the formula to "C". +1.
    – ysap
    Apr 16, 2012 at 14:32
  • I actually did not realize that Excel does implicit type conversion. Thanks for that.
    – ysap
    Apr 16, 2012 at 14:39
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
  • It does refer to the required cell, but unfortunately, on EXCEL 2010 it does not follow a moved cell.
    – ysap
    Apr 16, 2012 at 14:37
  • Not sure what you mean by "it does not follow a moved cell" - B:B in that formula behaves like any other cell reference in any other formula. If you drag it across or copy and paste the reference will change depending on the location to which you copy/drag it - if you cut it and paste then it doesn't change Apr 16, 2012 at 15:17
  • I took the line from your post as-is and copied it to cell P3. In cell B2 I have the number 16. Then P3 shows 16, as expected. I then dragged-and-dropped cell B2 to C2. Now B2 is empty and C2 has that 16. In P3 I still have the original formula, pointing to B2 and now showing an empty value, just as in B2. If P3 contained "=B2", then when dragged, the formula changes automatically to "=B3". I wanted your formula to change to =INDEX(C:C,3-1). I hope this clears my intention.
    – ysap
    Apr 16, 2012 at 18:10

You must log in to answer this question.

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