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

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

Hi Tom,

Here's a suggestion which may assist, it's not very technical, but it
works for me, so... asuming original data on sheet 1.

before deleting any rows/columns,

I would copy either the entire sheet1 or the range of cells containg
your lookup formula's to a new (tempory) sheet.

I personally would only copy the specific range keeping the addresses
the same, but on a new sheet. ie copy - sheet1 a5:b12 into sheet2
range a5:b12.

Then I would perform my row/column deletion on the original data
(sheet1)

finally copy the formula's back from your newly created temp sheet into
the original doc, and the formula's should remain unchanged.

a couple of VBA lines will deal with this process and delete the temp
sheet for you quite effectively - or simply record the process.

Hope this helps.

Regards


---
Message posted from http://www.ExcelForum.com/

Thanks, for the responses. Not sure I understand completely what to
do..

Here basically is the layout of the workbook,

Sheet1 - Lots of data, pasted in via a VBA macro with various columns
then deleted

Sheet2 - Lots of vlookup formulas created manually after the data in
sheet1 had its columns deleted. These I want to stay the same each
time I run the macro (of course thats another option, use VBA to
populate the sheet with the formulas, but I have 26 of them filled
down to various places, and they are rather long, so I'd rather not)

So anyway, is your idea to make a copy of my formula sheet or the data
sheet? I'd have thought excel will keep trying to be clever even if I
make copies. I'll have an experiment and see what I can do..


Skyway wrote in message ...
Hi Tom,

Here's a suggestion which may assist, it's not very technical, but it
works for me, so... asuming original data on sheet 1.

before deleting any rows/columns,

I would copy either the entire sheet1 or the range of cells containg
your lookup formula's to a new (tempory) sheet.

I personally would only copy the specific range keeping the addresses
the same, but on a new sheet. ie copy - sheet1 a5:b12 into sheet2
range a5:b12.

Then I would perform my row/column deletion on the original data
(sheet1)

finally copy the formula's back from your newly created temp sheet into
the original doc, and the formula's should remain unchanged.

a couple of VBA lines will deal with this process and delete the temp
sheet for you quite effectively - or simply record the process.

Hope this helps.

Regards


---
Message posted from http://www.ExcelForum.com/

I got it! Of course deleting contents or cutting cells will not affect
formulas, so I tried cutting to a temp sheet.

My formulas are already set up nicely when I run my macro, so..

I copy the data not to the actual final sheet but to a temp sheet.
Do all the column deleting and so on.
Copy from the temp sheet to final sheet.
delete temp sheet

all done, no problems and can be nicely automated :)

Thanks for the help..