Quote:
Originally Posted by AT--o
Hi,
After cutting and pasting a column from a Table, the formulas that were referring to the mentioned column, stick to the now empty column.
For example I have a column named A, I cut it and paste it but the cells referring to A now refer to Column1 which is the default name excel assigns to the now empty column.
Thank you all.
|
Hello.
Have you used INDIRECT function at your formulas ?
If you have for exsample at cell C5 the following formula
=SUM(INDIRECT("A10:A100"))
it will always refer to range A10:A100, no matter which changes you
will make to the worksheet.
If you copy or cut the contents from that area to somewhwre else the
reference at cell C5 will remain pointing to a10:A100. This is the case even if
you remove the column A or remove some rows from your workbook.
I tested both direct and relative referrences to whole column A.
On both cases the formula at C5 changed its formula to point to new location correctly.
=SUM(A:A)
=SUM(A$:A$)
Don't use just column letters as names or other that kind of names which are
reserved for Excel's internal use. That might cause confuse or even erroneous
function of Excel.
***