Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate formulas when active sheet switches
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate formulas when active sheet switches
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. Matthew wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas won't recalculate | Excel Worksheet Functions | |||
Big delay to recalculate formulas | Excel Discussion (Misc queries) | |||
Don't show sheet switches when running macro | Excel Programming | |||
Filter Switches vs Sheet Protection? | Excel Discussion (Misc queries) | |||
Printing switches focus off proper sheet | Excel Worksheet Functions |