I know by deleting rows and columns the corresponding
formula's are changed. But if I just delete the contents
of the cells the formula's remain unchanged.
-----Original Message-----
Hi, I have a worksheet in a workbook, with loads and
loads of
different VLOOKUP formulas in, for the lookup table I use
absoloute
references with the $ sign..

Now, the problem. The vlookups refer to other sheets in
the workbook
that contain data, this data is pasted in from a
different workbook
and then various columns of this data is deleted. Of
course deleting
the columns causes excel to try and be clever and change
my formulas
so they still refer to the same cells which ultimately
means the
vlookups now have the wrong table and I get #REF!s
everywhere..

So far I can think of 2 solutions which I don't
particularly want to
do.. Do the deleting of columns in the datas source
workbook, the
reason I don't want to do this is that I've set up loads
of named
ranges in there to make the copying easier, so I'd have
to re-do all
of those too, which would take ages (there are alot of
ranges).

Other option is to set up all my formulas so that
although they are
wrong when the data is pasted in to the data sheet,
deleting the
relevant columns makes them right, this I don't want to
do because it
would be alot of work (alot of different formulas) and
also probably
quite tricky to work out what the formula should be.

I'm hoping theres just some sort of setting I can choose
to stop excel
trying to "help" me out by adjusting the formulas
automatically, but
I'm not sure..
.