Moving cells without changing their formulas

  • Thread starter Thread starter juliejg1
  • Start date Start date
J

juliejg1

I have a spreadsheet with numerous columns of formulas. I had to take out
the columns A-V which left about 25 columns with formulas. I would like to
move the remaining columns to start in column A but do not want to have to go
through all of the formulas and change their references. The formulas are
quite lengthy and have only some absolute references and also reference other
worksheets. Is there a way to delete columns A-V without the remaining
formulas changing?
 
If the cells contain only absolute references and references to other
worksheets, the formulas will not change when you delete columns. $W$17 will
still say $W$17. Sheet2!A1 will still say Sheet2!A1. It may be that this is
not what you want, but (unless I've misunderstood) it's what you asked for.
Could you please explain, perhaps with an example of a formula (and a cell
reference where that formula is) and what you want to end up with.
 
I think there may be some confusion, Stephen. If a formula refers to $W$17,
but a column to the left of column W is deleted, then what was W17 becomes
V17, and the formula will change to refer to $V$17. I would have assumed
that this is what the OP wanted. The situation in which the references
won't automatically be corrected if columns to the left are deleted would be
if the formula said =INDIRECT(W16), and W16 contained the text $W$17.
 
Thank you for this correction, David. What you say (about $W$17) is what I
was thinking when I posted, but I quite agree that it's not what I said.
What I said is not correct and I apologise for any confusion caused. I
should slow my typing to keep pace with my thinking!

I am grateful for your reference to INDIRECT, as this is a possible reason
why the OP may not want what actually happens when columns are deleted,
which is what I could not fathom.
 
Back
Top