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) |
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 |
All times are GMT +1. The time now is 12:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com