Depends what you mean by better :-)
If you mean shorter/simpler and A1 style, then shoover s answer is fine:
=INDIRECT("$D"&ROW()+1)
If you prefer R1C1 style (easier to read IMHO ;-) ) then an even shorter/simpler/faster solution is:
=INDIRECT("R[1]C4",)
However, if you re after the fastest solution, or just simply prefer a non-volatile one, then a Named Formula is the way to go:
Define a Name, say Col4Down1, and set its value to:
=INDEX(!$D:$D,ROW()+1)
Place the following formula in a cell to get the desired result:
=Col4Down1
This works because of a little known quirk when using the bang operator ! in a Named Formula. When you don t specify a sheetname, !$D:$D always refers to the fourth column irrespective of column deletions/insertions. Think of it as absolute-absolute addressing.
Finally, Lance Roberts answer, whilst being non-volatile, suffers from a couple of problems. As he mentions, it will only work in certain, predetermined, rows. Secondly, insertion/deletion of any columns from A-D, or of any rows above, will break it. Modifying it to fix these leads to the following (if entered into cell B2):
=INDEX(B:B:2:2,ROW()+1,4)
or if you prefer R1C1 style and have set your spreadsheet to use this style:
=INDEX(R:C,ROW()+1,4)
