VLOOKUP to external references not updating properly

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top