G
Guest
Guest
I have no doubt several on this forum could propose a better solution to the problem stated below, but if not, I need to add code that triggers a recalc when a different sheet is selected.
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:\1JF\HR\[Employee info.xls]Summary'!$A$3:$B$25,2,FALSE))
=VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:\1JF\HR\[Employee info.xls]Summary'!$A$3:$D$25,4,FALSE)
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:\1JF\HR\[Employee info.xls]Summary'!$A$3:$B$25,2,FALSE))
=VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:\1JF\HR\[Employee info.xls]Summary'!$A$3:$D$25,4,FALSE)