refer to same row in other columns by using entire column in formula

[brzak]

Context
I have lots of columns of data which I want to do calculations on,
and i always want to consider the same row only.

I would like to use names in the formulas to make it easier to keep
track of which columns are being used. So it's not as if I have
a problem at the moment, just that it could be easier to follow if
i used named ranges.

Now, I noticed a 'feature' of excel and was wondering
if it is safe to use. Here it is:

Column A has, say, row numbers in it, so A1 has a 1, A2 a 2, etc.

If in cell B1 I type: '=A:A', the result is 1.
Below is cell the formula is typed into and the result:
C1, 1
C2, 2
C3, 3
C4, 4
IV12, 12

If i type '=A:A^2', the results in the following cells are:
C1, 1
C2, 4
C3, 9
C4, 16
IV12, 144

Does anyone know if I can trust this?
This looks too good to not have some major drawback....

Thanks for any tips on this

Brz

 

[Bernard Liengme]

I urge you not to use this non-standard use of full column references like
=A:A
Try changing one of the numbers you have in A1:A4 and not that the
corresponding cell in C does not update until the worksheet is
recalculated - by pressing f9 or by editing the formula in the C cell.
best wishes

 

[brzak]

Strange.... I did check that initially and I am finding that it does
update.
(on xp, excel 2002...)