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