Thread: Lookup returning old values

This involves looking up data on a server.
I have this formula in numerous cells in column D of my worksheet. It tells me how many of $A74 we currently have in production and returns a zero if there aren't any:

=IF(ISERROR(VLOOKUP($A74,'K:\Customer Service\OOData\[OPENJJLU.xlsx]All Books'!$B$11:$C$2000,2,FALSE)),0,VLOOKUP($A74,'K:\Customer Service\OOData\[OPENJJLU.xlsx]All Books'!$B$11:$C$2000,2,FALSE))

Often, it returns the values from previous days.
For example right now it is returning 8 for this item, yet when I open the lookup workbook and look at what is actually in production for that item I find it in cell C13 and it reads 12.

If in my working sheet I postion my cursor at the end of the formula and hit enter ...the correct value is returned.

I don't understand this because my calculation option is Automatic
Other cells in the sheet that are looking up data from that same workbook and other workboods are returning the correct information.

I have repeatedly re-written the formula using the 'point to' method to ensure it connects to the right file etc., but to no avail...as I keep having this problem. Some days it works and other days it doesn't.

Our server group can't figure out what the problem is and keep telling me Excel is simply an unreliable tool to use to manage valuable programs.

I'm on W7 Pro, and we're on Microsoft servers.

Any ideas would be much appreciated!

Thanks

Originally Posted by mrice

You could try forcing a re-calculation when you workbook opens by inserting the following code into the ThisWorkbook tab in the VBA editor (Alt F11).

Private Sub Workbook_Open()
Calculate
End Sub

Thanks for that Martin. Still didn't work. I discovered the problem arises when I have more than on Excel session open. Our IT department had suggested using starting a new Excel session for each major workbook being opened because crashes were crashing many at once. If a source table is open in one workbook and changes are made to it ...they aren't being reflected in the workbook looking it up until both workbooks are closed and saved and re-opened. I've gone back to using one session for all my open workbooks and just trying to cut down on the number open at any given time.
I also had to turn off auto save because if a workbook crashed with it on ..all the lookup formulas changed from looking up on the server ...to looking up on my C:\drive even though Office is set with my home directory being on the server. When it loses the server for even a heartbeat, it crashes and auto save will diabolically change lookup references. Very ugly process.
Jay