VLOOKUP to external references not updating properly

[pilot]

My workbook contains a number of sheets, each with many formulas including two that reference another file with a VLOOKUP. When I change to a different sheet, the two cells whose formulas include VLOOKUP continue to display the result from the previously selected sheet until a recalc occurs. I can force an update by F9 or by entering something in another cell which triggers the recalc.

Should the two cells in question update when the sheet they're on is selected? Or must I add some code to force a recalc when a new sheet is selected?

This probably has something to do with the two formulas using the sheet name to determine where the VLOOKUP occurs. I'll post the formulas below. Maybe there's a better way to do the same thing without creating the problem described above.

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))&" "&(VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:1JFHR[Employee info.xls]Summary'!$A$3:$B$25,2,FALSE))

=VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:1JFHR[Employee info.xls]Summary'!$A$3:$D$25,4,FALSE)

[pilot]

Bumping this to the top of the list one time in hopes of response.