Forum Discussion
PROPER case issue converting existing large amount of rows without copying text into a new column
nashpromedcomau You should NOT delete column A as it will break the formulas in column B that refer to cells in column A. That's the way it is. The #REF error will occur. Not sure what "official MS instruction" you have read.
What you need to do is copy column B (the one with the PROPER formulas in it) and paste it as values on top of column A and then delete column B with the formulas as it is no longer needed.
nashpromedcomau I suspect that your Excel version doesn't support dynamic arrays, otherwise you should be able to enter =PROPER(X1:X12000) in e.g. Y1 and spill the result to the entire range Y1:Y12000 in one go.
If you can't, then you have no other option than to enter =PROPER(X1) in Y1, double-click on the small square in the bottom right-hand corner of the cell to fill the formula all the way down to the row where column Y ends. That doesn't take much time.
You say "once you move it across it is still referring to the previous column". what exactly are you trying to achieve? If you want to copy the formula across but still have it refer to column X, make the reference partly absolute, i.e. =PROPER($X1:$X12000) or =PROPER($X1) by putting the $ sign before the column references.
If all of this makes no sense, perhaps you can upload a file that demonstrates the problem.