Recalculate formulas when active sheet switches

[Matthew]

I'm working on a simple ledger workbook, but I'm having trouble with a
formula that does a vlookup based upon the sheet's name. The trouble is that
when it updates, I then switch to a copy of the sheet (with a changed sheet
name), but the formula doesn't update, and keeps the same value as that of
the last sheet.

How can I have excel recalculate the formulas when the active sheet is
changed?

Formula that isn't updating:
=VALUE(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255))&" -
"&VLOOKUP(VALUE(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)),
Summary!A5:F38, 2)

 

[Dave Peterson]

Try changing all these strings so that they include the cell that holds the
formula:

If the formula is in x9, then
CELL("filename")
becomes
CELL("filename",x9)

If you don't specify a range, then excel will use the workbook/sheet that's
active when it recalcs.

I'm working on a simple ledger workbook, but I'm having trouble with a
formula that does a vlookup based upon the sheet's name. The trouble is that
when it updates, I then switch to a copy of the sheet (with a changed sheet
name), but the formula doesn't update, and keeps the same value as that of
the last sheet.

How can I have excel recalculate the formulas when the active sheet is
changed?

Formula that isn't updating:
=VALUE(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255))&" -
"&VLOOKUP(VALUE(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)),
Summary!A5:F38, 2)