Moving cells without changing their formulas
 

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?

Moving cells without changing their formulas
 

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.

"juliejg1" wrote in message
...
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?

Moving cells without changing their formulas
 

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.
--
David Biddulph

"Stephen" <none wrote in message
...
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.

"juliejg1" wrote in message
...
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?

Moving cells without changing their formulas
 

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.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Stephen" <none wrote in message
...
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.

"juliejg1" wrote in message
...
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?