Excel 2007 : Lookup returning old values

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