PDA

View Full Version : VLOOKUP not updating on copied sheets



Eric0568
12-16-2007, 10:12 AM
I have a named range named "Matrix" on a sheet called "Matrix" that I look up values from with a VLOOKUP in another sheet.
The command works perfectly on the sheet that I originally programmed.
When I make a COPY of that sheet, however, there is a problem.
If I go make a change in the Matrix, and view the results on the original sheet, it is updated correctly. If I view the results on the COPIED sheet, it does not update until I do one of two things:

1. Save, close, and reopen the file (no, saving alone does not do it).
2. Cursor in the formula bar and hit enter, forcing an update of that cell (and that cell alone).

I know that Excel does not recalculate cells unless something has changed about the cells that the cell in question references, but I have changed the information that the VLOOKUP cell references, so it should re-calc.
The weird thing is that it DOES on the original sheet, but not the copied sheet.

Neither the cell that contains the VLOOKUP, nor the cell that it references contains any VBA routines, so that is not it either.
And my calculation is set to auto.

Much thanks in advance.

:help

</IMG>

Bob Phillips
12-16-2007, 10:24 AM
Which sheet are you copying, Matrix, or the sheet with the lookup on? The latter works fine for me.

Eric0568
12-16-2007, 10:30 AM
I am copying the sheet with the vlookup command in it.
Yeah I have tested the problem out on a simplified situation, and it works fine for me too. Can't figure out why it doesnt work in this more complex file.
I will have to bring home a copy of the file and attach it for you to look at.

Bob Phillips
12-16-2007, 11:15 AM
Sounds like the best idea.

Eric0568
12-17-2007, 10:31 PM
Yeah... um...
Yknow what?
My file is about 10x the server limit even when zipped.
Is there any way you could PM me with an email address, I could try to send you the file that way.

Bob Phillips
12-18-2007, 02:38 AM
Can you not create a sample workbook that demonstrates the problem?

Eric0568
12-18-2007, 10:07 PM
well...
If I strip it down to the point that I can attach it to this board, it works fine.
It has something to do with the size or complexity of the file that is causing the problem.
Perhaps there is a limit to the amount of programming that a file can contain, and after that limit then not all re-calcing can be accommodated.
I don't know. The problem is more of an inconvenience, not something that will make the file unusable.
I have a version that is stripped down as far as I can, and still be usable. It is still about 6.5k, so I can't attach it here. If you want to PM me an email addy, I can send you a copy.

unmarkedhelicopter
12-19-2007, 03:55 AM
And when you zip it ?

Eric0568
12-19-2007, 06:44 AM
I actually dont have WinZip at the moment, I have WinRar. But I expect the compression ratio is similar. I can get it down to about 1meg but with a .rar extension, which the board does not support.
It would still be too big to fit inside the puny 244.1k limit :banghead:
I guess I could buy winzip and try, but wouldn't I have the same problem?
If I get it down to a size that can be zipped and attached, it doesnt have the problem.

unmarkedhelicopter
12-19-2007, 07:00 AM
I have had problems with worksheets in the past that have a smal corruption in them, when you delete enough crap (and the corruption) it starts working again. All I can suggest it that you copy (not a sheet (that ALWAYS copies the corruption), but the stuff within it) to a new workbook, copy the code and then you "should" have everything you had before but without the problem. This is not a guarantee though :(